select into

Question

select into

sunshine-587009

SSCrazy Eights

Points: 8981
More actions
July 16, 2008 at 8:41 am

#103532

Is it possible to create a select into statement in which it is inserted into the new table in the exact same order as the original?
Every time I run a select into, it is not in the same order. Is it because I use select table.*?
Any help appreciated?
¤ §unshine ¤

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

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

Jack Corbett SSC Guru Points: 184394 More actions · Answer 1

Is there a clustered index on the source table? If the table is a heap then SQL server will use the fastest access path. You can use order by in your select into, if you there are columns that are sortable like a create date or identity or incrementing column.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

sunshine-587009 SSCrazy Eights Points: 8981 More actions · Answer 2

No indexes at all on the table. It is just a plain old heap table. If it were up to me, I add an index or PK on it, unfortunately not my design. If I use the orderby no matter which column i use, it still wont be in the same order as the original. Just wondering if there was an alternative.

¤ §unshine ¤

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

You said "It's not in the same order" Do you mean when retrieving?

Tables are considered unordered in SQL. Unless you specify an ORDER BY in a select, there are no guarantees made on the order of the rows returned.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

sunshine-587009 SSCrazy Eights Points: 8981 More actions · Answer 4

Correct. As many times as I do a select top on the original, it is always the same. When I do the same on the new table, it is not in the same order as the original, but always comes up in the same order itself.

¤ §unshine ¤

Grant Fritchey SSC Guru Points: 398909 More actions · Answer 5

The problem is, even if you order the SELECT statement when doing the inserts, when you next SELECT from the table, you're not guaranteed the order it comes back in. Even if you put a clustered index on it, as Gail has already said, without an ORDER BY statement, you're not guaranteed it will come back in the order of the index.

"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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 6

It does normally work out that way, though I have seen cases (on a large table on a server with multiple procs) where the order could come back in different ways, depending whether or not a parallel scan was used.

Bottom line though, unless you specify an order by, you cannot say anythng about the order of the rows.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

sunshine-587009 SSCrazy Eights Points: 8981 More actions · Answer 7

Alright. Thank you so much for all of your help.

¤ §unshine ¤