Submit My Question - Recent Questions - Recent Answer
Msdn Tech >> SQL Server >> Transact-SQL

INSERT INTO as SELECT with ORDER BY

Hi,

This may sound like a dumb question, but I need to be certain of the answer.

If I have a query like this:

INSERT INTO table1
(col1, col2, col3, ... )
SELECT col4, col5, col6, ...
FROM table2
ORDER BY col7, col8, col9, ...

and table1 has an identity column that increments by 1 each time, am I gauranteed that the records inserted into table1 will always be inserted in the order as specified in the ORDER BY clause and hence the increasing identity column in table1 will reflect the same order as that of the ORDER BY clause?

Seems like it should be the case but I need to be sure.

Thanks,

Peter

[665 byte] By [PeterWojnar] at [2007-12-22]
«« Administrator gets 403 - access denied
»» DLLs unloaded
# 1
The only guarantee is that the identity values will be generated based on the ORDER BY clause. But there is no guarantee for the order of insertion of the rows into the table. You should think of DML operations and SELECT statements as SET operations so there is no order to the rows. For more details on the ordering guarantees, see the blog post below:

http://blogs.msdn.com/sqltips/archive/2005/07/20/441053.aspx

UmachandarJayachandran-MS at 2007-8-30 > top of Msdn Tech,SQL Server,Transact-SQL...
# 2

Thank you for the reply.

If I understand your answer and the blog correctly, it appears that I should get what I expect. Here's my interpretation. Please let me know if I'm misunderstanding something.

If the identity values are generated and assigned in the same sequence as the ORDER BY, then the order of the identity column should retain the ORDER BY order. If the rows are not physically inserted in that order it does not really matter. But if I query table1 ordering by the identity column then I should see the same relative order as the ORDER BY clause from the SELECT * FROM table2 query.

PeterWojnar at 2007-8-30 > top of Msdn Tech,SQL Server,Transact-SQL...
# 3
Yes, this is correct.
UmachandarJayachandran-MS at 2007-8-30 > top of Msdn Tech,SQL Server,Transact-SQL...
SQL Server
Transact-SQL

SQL Server Hot Topic

SQL Server New Topic

SQL Server

Java / MSDN / Linux Tech / PHP Tech / MSDN DotNet / MSDN TECH / Health Library / Programmers Tech / visual basic Tech / Developer Tech / Ipod faqs / Msdn China Tech

Site Classified