TOP clause in INSERT statement

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

General SQL Server Forums

New to SQL Server Programming

TOP clause in INSERT statement

Author

Topic

mmkrishna1919
Yak Posting Veteran

95 Posts

Posted - 2013-06-25 : 04:36:01

Hi Team,
Data Base:AdventureworksLT2008

will below two statements insert same set of rows in a table?
insert top(5) into #test(id,name)
select CustomerID,firstname from SalesLT.Customer
order by CustomerID desc

insert into #test(id,name)
select top (5) CustomerID,firstname from SalesLT.Customer
order by CustomerID desc

And how optimiser will choose 5 rows to insert in first statement
Are those first 5 rows in result set of select statement?
Or
It will pick randomly?

Thanks.

M.MURALI kRISHNA

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-25 : 04:41:14

It will choose 5 random rows (ie order not guaranteed) though actual order may depend on factors like presence of clustered index etc. Ie effect of using TOP N in insert will be to ignore the ORDER BY in select

In the second case its obvious, ie based on ORDER specified in ORDER BY

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

mmkrishna1919
Yak Posting Veteran

95 Posts

Posted - 2013-06-25 : 04:51:59

Thanks Visakh..

M.MURALI kRISHNA

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-25 : 04:55:03

welcome

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources