Insert into..Select with order by

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2012 Forums

Transact-SQL (2012)

Insert into..Select with order by

Author

Topic

Luuk123
Yak Posting Veteran

52 Posts

Posted - 2013-07-08 : 03:25:59

Hi all,

I'm having a problem with inserting data into a table in specific order.
My query is like 'Insert into tab (col_id) select (col_id) from tab2 order by col_id'.

This doesn't work. Is it illigal to use an order by in a select into statement?

Thanks!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-07-08 : 03:34:07

it is not. you do have unwanted braces though

ie this is enough


Insert into tab (col_id) 
select col_id 
from tab2 
order by col_id

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Luuk123
Yak Posting Veteran

52 Posts

Posted - 2013-07-08 : 03:41:25

oh stupid I see. So it is legal to use an order by in an insert statement. Thanks!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-07-08 : 03:45:29

yep...it is...
Only thing is that it wont guarantee that results from destination table (tab in above case) is ordered unless you use an explicit order by in the select statement which is used to retrieve data from it.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Luuk123
Yak Posting Veteran

52 Posts

Posted - 2013-07-08 : 03:56:51

Ok. It's necessary to insert the data in specific order so I can't use a regular order by. Is that what you mean?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-07-08 : 04:02:22

Nope...I meant while selecting from table.

Inserting using order by will make sure id column (identity) will have numbers generated in the same order.
My point was while selecting you still need to use order by in the select to get the result in order.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

sateeshGenpact
Starting Member

6 Posts

Posted - 2013-07-08 : 05:23:12

use this way :

Set Identity_insert ON;

Insert into Tab
Select Row_number() over(order by col_id) Identity_col,Col_id from tab2

Set Identity_insert OFF;

if seed already started then add same number row_number function.

Sateesh

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-07-08 : 08:40:28

If you want to be absolutely certain that the values in the identity column are in the order specified by col_id, use the method Sateesh suggested. When you use the ORDER BY clause in the select statement part of your query, even though the input to the insert statement are correctly ordered, there is no guaratee that SQL Server will insert the rows in that order. In most cases it will insert them in that order, but there is always a possibility it won't, especially if MAXDOP > 1. So it is safer to use Sateesh's suggestion.

Of course, as Sateesh mentioned, you would need to look at the existing data in the table and make sure that you are not colliding with existing constraints.

Luuk123
Yak Posting Veteran

52 Posts

Posted - 2013-07-08 : 08:54:17

Thanks guys!

I'm gonna use the method sateeshGenpact suggets.

Thank you!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-07-08 : 11:05:57

quote:
Originally posted by James K

If you want to be absolutely certain that the values in the identity column are in the order specified by col_id, use the method Sateesh suggested. When you use the ORDER BY clause in the select statement part of your query, even though the input to the insert statement are correctly ordered, there is no guaratee that SQL Server will insert the rows in that order. In most cases it will insert them in that order, but there is always a possibility it won't, especially if MAXDOP > 1. So it is safer to use Sateesh's suggestion.

Of course, as Sateesh mentioned, you would need to look at the existing data in the table and make sure that you are not colliding with existing constraints.

Sorry but this article suggests different

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

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2013-07-08 : 13:15:46

quote:
Originally posted by visakh16

quote:
Originally posted by James K

If you want to be absolutely certain that the values in the identity column are in the order specified by col_id, use the method Sateesh suggested. When you use the ORDER BY clause in the select statement part of your query, even though the input to the insert statement are correctly ordered, there is no guaratee that SQL Server will insert the rows in that order. In most cases it will insert them in that order, but there is always a possibility it won't, especially if MAXDOP > 1. So it is safer to use Sateesh's suggestion.

Of course, as Sateesh mentioned, you would need to look at the existing data in the table and make sure that you are not colliding with existing constraints.

Sorry but this article suggests different

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

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Is there a particular section you are refereencing in that article Vis?

The way I'm reading what James wrote and what the article is saying, appear to be very similar to me. But, I'm probably missing something. :)

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-07-08 : 13:26:47

Lamprey, the issue that I was thinking about was in SELECT INTO. In SELECT INTO construct, the order is not guaranteed. In INSERT..SELECT..ORDER BY, construct, the order is guaranteed. I was thinking about the SELECT INTO issue when I posted that.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2013-07-08 : 13:39:34

Ahh got it.. thanks. :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources