Order by Clause does not work with Select * into #temp statement

Question

Order by Clause does not work with Select * into #temp statement

sushil.tuladhar

SSC Veteran

Points: 286
More actions
July 23, 2009 at 2:28 am

#134142

Hi,
I have a query as follows,
SELECT IDENTITY(int, 1,1) AS RecId, FROMTerm
INTO #TempRates FROM Rates
ORDER BY FromTerm
I was expecting the result from #temprates table as
1 1
2 3
3 6
4 9
5 12
But it returns
1 1
2 12
3 6
4 9
5 3
Anyone has any idea why this order by clause does not work when I do Insert into #tempRates with Idendity Column?
BTW this happens only in I am using SQL2000
In SQL2005, it returns correctly
Thanks,

Viewing 8 posts - 1 through 7 (of 7 total)

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

Carl Federl One Orange Chip Points: 25384 More actions · Answer 1

The case that the rows are inserted in an order that matches a specificed "order by" clause is coincidental and is documented in Books OnLine

When used together with a SELECT...INTO statement to insert rows from another source, the ORDER BY clause does not guarantee the rows are inserted in the specified order.

The solution is not to use identity,but to use the RANK window function with an order by.

SQL = Scarcely Qualifies as a Language

terrance.steadman SSCrazy Points: 2097 More actions · Answer 2

Greetings,

Microsoft does not use the ORDER BY clause when inserting records into any table, including table valued functions. This happens also in SQL 2005. Instead, their SQL engine will decide what is the best order to insert the records into the table. So, if you want the records in a specific order when you insert them into a table, then you may need to insert them one at a time. The other option is to just use the ORDER BY clause when you read the table.

ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/1054c76e-0fd5-4131-8c07-a6c5d024af50.htm

Near the top, under the TOP(expression) section.

** The rows referenced in the TOP expression that are used with INSERT, UPDATE, or DELETE are not arranged in any order.**

There is another section that details better how the ORDER BY is ignored, but I am not able to find it at this time in the help document.

Have a good day.

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 3

Carl Federl (7/23/2009)
The case that the rows are inserted in an order that matches a specificed "order by" clause is coincidental and is documented in Books OnLine
When used together with a SELECT...INTO statement to insert rows from another source, the ORDER BY clause does not guarantee the rows are inserted in the specified order.
The solution is not to use identity,but to use the RANK window function with an order by.

Here's the interesting thing though. If you were to flip that around and put an identity column on the temp table FIRST, then did the insert using the order by, one of the MS dev guys on the relevant team (Conor I believe) did confirm that the order by would guarantee that the identity values would be assigned in the order of the order by.

so no - the actual order in which they are inserted is not guaranteed, but the values are supposed to be.

There is a support ticket on just this behavior:

http://support.microsoft.com/kb/273586

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Carl Federl One Orange Chip Points: 25384 More actions · Answer 4

Rather than try to figure out when identity values will or will not be sequentially assigned based on an ORDER BY, suggest using RANK, which always produces a sequence with no gaps and in the desired order.

use tempdb

go

SELECTRANK() OVER (ORDER BY name ) AS NameId

,name

INTO#temp

FROMsys.objects

WHEREname NOT LIKE '#%'

SELECT*

FROM#TEMP

ORDER BY NameId

SQL = Scarcely Qualifies as a Language

Carl Federl One Orange Chip Points: 25384 More actions · Answer 5

Sorry,my mistake - stated "RANK", which will not work ! You need to use ROW_NUMBER.

SELECTROW_NUMBER ( ) OVER (ORDER BY name ) AS NameId

,name

INTO#temp

FROMsys.objects

WHEREname NOT LIKE '#%'

SELECT*

FROM#TEMP

ORDER BY NameId

SQL = Scarcely Qualifies as a Language

Jeffrey Williams SSC Guru Points: 90304 More actions · Answer 6

Carl Federl (7/23/2009)
Sorry,my mistake - stated "RANK", which will not work ! You need to use ROW_NUMBER.
SELECTROW_NUMBER ( ) OVER (ORDER BY name ) AS NameId
,name
INTO#temp
FROMsys.objects
WHEREname NOT LIKE '#%'
SELECT*
FROM#TEMP
ORDER BY NameId

I may have mis-read this, but isn't the OP having a problem with this on SQL Server 2000? I thought I read that...could be mistaken though...:-D

Either way, using ROW_NUMBER() is the correct way of doing this in SQL Server 2005 and greater.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

sushil.tuladhar SSC Veteran Points: 286 More actions · Answer 7

Hi All,

Thank you so much for your reply.

Alternatively, I tried,

1. Created a Temp Table with Identity column

2. Then insert into the table from the query with order by clause

It works!