Insert into #tempTable exec sp_executesql

Question

Insert into #tempTable exec sp_executesql

b_hitesh81

SSC Veteran

Points: 270
More actions
November 2, 2007 at 4:37 am

#180907

Hello,
Insert into #tempTable exec sp_executesql @sql.
this statement worked fine till the data was small but behaviour changed when data got bigger.
The problem is that i have an order by clause in @sql and i want to insert in #tempTable in that order only. but the statement
Insert into #tempTable exec sp_executesql @sql
after execution fills the #tempTable randomly instead of the in the order specified in the @sql.
Please help
Hitesh Jain

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Andras Belokosztolszki SSC-Insane Points: 22031 More actions · Answer 1

Databases work with sets/bags. There is no guarantee that the order in which you insert data into a table is the order in which you will get it back. What you could do is to have an identity column on the temptable

(identitycolumn int identity(1,1))

and then order the results when you query the table based on this column

Order by identitycolumn

Regards,

Andras

Andras Belokosztolszki, MCPD, PhD
GoldenGate Software

Johannes Fourie Default port Points: 1470 More actions · Answer 2

Another option is to create an index on the temp table that you want to use for sorting before inserting into it.

Then remove the ORDER BY in the initial select statement, but add the ORDER BY clause to the query that later selects out of the temp table.

You may gain some performance as the select statement does not have to order the results. Unless if the select statement's ORDER BY clause is already using a predefined INDEX.

Grant Fritchey SSC Guru Points: 399278 More actions · Answer 3

Johannes Fourie (11/2/2007)
Another option is to create an index on the temp table that you want to use for sorting before inserting into it.
Then remove the ORDER BY in the initial select statement, but add the ORDER BY clause to the query that later selects out of the temp table.
You may gain some performance as the select statement does not have to order the results. Unless if the select statement's ORDER BY clause is already using a predefined INDEX.

I have to agree with this approach. Putting the ORDER BY in the initial SELECT, but then having a second SELECTwithout an ORDER BY makes it unlikely you can ever be sure of how the data will be returned. Put the ORDER BY in the second SELECT.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

b_hitesh81 SSC Veteran Points: 270 More actions · Answer 4

Hello,

Thank you Andras.

The solution you gave worked.

Thank you again.

Hitesh Jain

Solu-Soft Technologies Pvt. Ltd.