SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2012 Forums
 Transact-SQL (2012)
 Insert into..Select with order by
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Luuk123
Yak Posting Veteran

52 Posts

Posted - 07/08/2013 :  03:25:59  Show Profile  Reply with Quote
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

India
52323 Posts

Posted - 07/08/2013 :  03:34:07  Show Profile  Reply with Quote
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
Go to Top of Page

Luuk123
Yak Posting Veteran

52 Posts

Posted - 07/08/2013 :  03:41:25  Show Profile  Reply with Quote
oh stupid I see. So it is legal to use an order by in an insert statement. Thanks!
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52323 Posts

Posted - 07/08/2013 :  03:45:29  Show Profile  Reply with Quote
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
Go to Top of Page

Luuk123
Yak Posting Veteran

52 Posts

Posted - 07/08/2013 :  03:56:51  Show Profile  Reply with Quote
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?
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52323 Posts

Posted - 07/08/2013 :  04:02:22  Show Profile  Reply with Quote
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
Go to Top of Page

sateeshGenpact
Starting Member

India
6 Posts

Posted - 07/08/2013 :  05:23:12  Show Profile  Reply with Quote
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
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3712 Posts

Posted - 07/08/2013 :  08:40:28  Show Profile  Reply with Quote
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.
Go to Top of Page

Luuk123
Yak Posting Veteran

52 Posts

Posted - 07/08/2013 :  08:54:17  Show Profile  Reply with Quote
Thanks guys!

I'm gonna use the method sateeshGenpact suggets.

Thank you!
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52323 Posts

Posted - 07/08/2013 :  11:05:57  Show Profile  Reply with Quote
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
Go to Top of Page

Lamprey
Flowing Fount of Yak Knowledge

4614 Posts

Posted - 07/08/2013 :  13:15:46  Show Profile  Reply with Quote
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. :)
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3712 Posts

Posted - 07/08/2013 :  13:26:47  Show Profile  Reply with Quote
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.
Go to Top of Page

Lamprey
Flowing Fount of Yak Knowledge

4614 Posts

Posted - 07/08/2013 :  13:39:34  Show Profile  Reply with Quote
Ahh got it.. thanks. :)
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.14 seconds. Powered By: Snitz Forums 2000