February 26, 2008 at 11:35 am
Order by in an insert have no meaning, and if you don't specify the order in a select, no order is guaranteed.
Rather do it this way.
insert into #table
select * from table
select * from #table
order by col1
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 26, 2008 at 12:15 pm
GilaMonster (2/26/2008)
Order by in an insert have no meaning, and if you don't specify the order in a select, no order is guaranteed.
True with one caveat - ORDER BY during an insert would guarantee the values doled out by any Identity field in the destination table. It doesn't guarantee physical order of insert though. Meaning - it will ensure that your identity values will be assigned in order of the ORDER BY, but not the order in which they are actually stored.
----------------------------------------------------------------------------------
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?
February 26, 2008 at 12:36 pm
Matt Miller (2/26/2008)
True with one caveat - ORDER BY during an insert would guarantee the values doled out by any Identity field in the destination table.
True. I always forget about that. Haven't been using identities for a while.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 3 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply