union all limits

[Former Member]

hi folks.

we had this question directed at us so i thought to move it on to this discussion to see if any one had some ideas. thanks.

I have a question to you: how many UNION ALL SELECT “raw literal” subqueries can I use in one SQL query to Sybase Server?

I need to insert a batch of strings to temporary table. The most pretty syntax for this (according to Sybase restrictions) is to use the following query: “insert #tmp select “string1” union all select “string2” union all select …”.

Sybase documentation says the following:
The maximum number of subqueries within a single side of a union is 50.

The total number of tables that can appear on all sides of a union query is 256.

And what’s about my case of query? I’ve successfully inserted 500 entries in such way but what about maximum possible number of inserts?

[former_member188958 · Accepted solution]

Looks like there is some limit around 2048, the following test script starts getting error 702 for values larger than 2048

isql -Usa -Ppassword -o foo.txt

set nocount on

declare @x int

select @x = 1

while @x < 2048

begin

--print "select convert(char(1000), 'a')  union all "

print "select rand2()  union all "

select @x = @x+1

end

--print "select 'a'"

print "select rand2()"

print "go"

go

exit

isql -Usa -Ppassword -i foo.txt

Msg 702, Level 20, State 1:

Server 'BB', Line 1:

Memory request for 16392 bytes exceeds the size of single page of 16384 bytes.

ASE is terminating this process.

-bret

[Former Member · Accepted solution]

thankyou !