During my recent training at one of the clients, I was asked regarding the enhancement in TOP clause. When I demonstrated my script regarding how TOP works along with INSERT, one of the attendees suggested that I should also write about this script on my blog. Let me share this with all of you and do let me know what you think about this. The script compares INSERT TOP (N) with a SELECT TOP (N) inside the INSERT.
Note that there are two different techniques to limit the insertion of rows into the table.
Method 1:
INSERT INTO TABLE …
SELECT TOP (N) Cols…
FROM Table1
Method 2:
INSERT TOP(N) INTO TABLE …
SELECT Cols…
FROM Table1
Today we will go over the second method which in fact is the enhancement in TOP along with INSERT. It is very interesting to also observe difference between both the methods. Let us play with one real example and we understand what exactly is happening in either of the case.
USE tempdb GO -- Create Table IF EXISTS (SELECT * FROM sys.objects WHERE OBJECT_ID = OBJECT_ID(N'TestValue') AND type IN (N'U')) DROP TABLE TestValue GO CREATE TABLE TestValue(ID INT) INSERT INTO TestValue (ID) SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 GO -- Select Data from Table SELECT * FROM TestValue GO -- Create Two Table where Data will be Inserted IF EXISTS (SELECT * FROM sys.objects WHERE OBJECT_ID = OBJECT_ID(N'InsertTestValue') AND type IN (N'U')) DROP TABLE InsertTestValue GO CREATE TABLE InsertTestValue (ID INT) IF EXISTS (SELECT * FROM sys.objects WHERE OBJECT_ID = OBJECT_ID(N'InsertTestValue1') AND type IN (N'U')) DROP TABLE InsertTestValue1 GO CREATE TABLE InsertTestValue1 (ID INT) GO -- Option 1: Top with Select INSERT INTO InsertTestValue (ID) SELECT TOP (2) ID FROM TestValue ORDER BY ID DESC GO -- Option 2: Top with Insert INSERT TOP (2) INTO InsertTestValue1 (ID) SELECT ID FROM TestValue ORDER BY ID DESC GO -- Check the Data SELECT * FROM InsertTestValue GO SELECT * FROM InsertTestValue1 GO -- Clean up DROP TABLE InsertTestValue DROP TABLE InsertTestValue1 DROP TABLE TestValue GO
Now let us check the result of above SELECT statements.
It is very interesting to see when Option 2 is used, ORDER BY is absolutely ignored and data is inserted in any order.
In future articles, we will talk about performance for these queries. What are your thoughts on this feature? Have you used INSERT TOP(N) in your application?
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
Discover more from SQL Authority with Pinal Dave
Subscribe to get the latest posts sent to your email.
24 Comments. Leave new
It is really a cool feature but I’m eager to see the performance of these two methods in your future articles. I don’t think 2nd method is bad over the other.
Yes. It is not bad. But Order is not gauranteed
Sir,
if it is ignoring Order by
what meaning of Second Method ?
any other purpose can we use this ?
The second method can be used randomly copy the rows so ORDER BY will be omitted automatically
I would like to know is it possible to insert a new row in first position in existing table using sql.
Please let me if it is possible
use AdventureWorks;
go
CREATE TABLE dbo.SickLeave1
(EmployeeID int, FullName nvarchar(100), SickLeaveHours int)
GO
INSERT TOP (10)PERCENT
SickLeave1
SELECT
EmployeeID, FirstName + ‘ ‘ + LastName, SickLeaveHours
FROM Person.Contact JOIN HumanResources.Employee
ON Person.Contact.ContactID = HumanResources.Employee.ContactID
ORDER BY SickLeaveHours DESC
go
CREATE TABLE dbo.SickLeave2
(EmployeeID int, FullName nvarchar(100), SickLeaveHours int)
GO
INSERT TOP (10)PERCENT
SickLeave2
SELECT
EmployeeID, FirstName + ‘ ‘ + LastName, SickLeaveHours
FROM Person.Contact JOIN HumanResources.Employee
ON Person.Contact.ContactID = HumanResources.Employee.ContactID
ORDER BY EmployeeID DESC;
select * from SickLeave1 order by EmployeeID;
select * from SickLeave2 order by EmployeeID;
drop table SickLeave1
drop table SickLeave2
Observe the ORDER BY cluase in both inserts….
Result: Same result from SickLeave1 and SickLeave2
–>The rows referenced in the TOP expression that are used with INSERT, UPDATE, or DELETE are not arranged in any order.
ref:http://msdn.microsoft.com/en-us/library/ms174335.aspx
/* But if you are so particular about the order, enclose your SELECT code in EXEC */
use AdventureWorks;
go
CREATE TABLE dbo.SickLeave1
(EmployeeID int, FullName nvarchar(100), SickLeaveHours int)
GO
INSERT TOP (10)PERCENT
SickLeave1(EmployeeID,FullName,SickLeaveHours)
EXEC (‘SELECT
EmployeeID, FirstName + ” ” + LastName, SickLeaveHours
FROM Person.Contact JOIN HumanResources.Employee
ON Person.Contact.ContactID = HumanResources.Employee.ContactID
ORDER BY SickLeaveHours DESC’)
go
CREATE TABLE dbo.SickLeave2
(EmployeeID int, FullName nvarchar(100), SickLeaveHours int)
GO
INSERT TOP (10)PERCENT
SickLeave2
EXEC (‘SELECT
EmployeeID, FirstName + ” ” + LastName, SickLeaveHours
FROM Person.Contact JOIN HumanResources.Employee
ON Person.Contact.ContactID = HumanResources.Employee.ContactID
ORDER BY EmployeeID DESC’)
select * from SickLeave1 order by EmployeeID;
select * from SickLeave2 order by EmployeeID;
drop table SickLeave1
drop table SickLeave2
PS:Sorry for lengthy comments.
Hi guys, i am new to this, plz help.
selecting all the columns from table A (i.e. select * from A) and inserting them into a dataset B which is already existing having the same column names.
How can i do that :( ?
method1: insert into B () select * from B
method2: select * insert into B from A
use method1 instead of method2…..method2 is usually used to create a new table whose columns and datatypes match the parent table
@Yogi
Insert into TableB (Column Names)
Select Column Names from TableA
~Peace.
Hi Yogi,
I frequently use “select * into ” for inserting into one table from another table with same column fields of same database or different database.
select * into tableA(columns) from tableB(columns)
Your can modify this if you are working with two database:
select * into DatabaseA.dbo.tableA from DatabaseB.dbo.tableA
Hope you will find this useful.
thanks guys ..
how we can insert the data in a join table by use of stored procedure
hi pinal.This code is not working in sql server 2000.
This is not supported in version 2000. Available only from version 2005
I’m absolutely appalled by the fact that it ignores the ORDER BY clause with no warning whatsoever. I am learning T-SQL and have found so many unintuitive ‘gotchas’ like this in the language. It is too easy to make unintended mistakes.
Yes it is true. ORDER BY is guaranteed only in SELECT statement
Great article.
I found this blog when I am looking for using Insert Top for the batch insert. Basically, I have the situation that requires insert 10 mil. Rows in a table. In order to avoid blocking, I have to using some sort of batch insert technique. Will this Insert Top help? Would you please give out an example?
Thanks.
Hello everyone,
I am new and trying to make use of Top 100, to limit the number of records which are inserted into my temp table, but getting error message(s)
Error 1 : Incorrect syntax near keyword ‘top’
insert top 100 into #TEMP_DATES
(
A,
B,
C,
D
)
select
1,
2,
3,
4
from
#TEMP_M_Date
Error 2 : Incorrect syntax near ‘100’
insert top (100) into #TEMP_DATES
(
A,
B,
C,
D
)
select
1,
2,
3,
4
from
#TEMP_M_Date
Please help me understand the syntax, I am using SQL advantage (and working on sybase)
Thanks!
The word “random” is often used incorrectly, so I have a hard time trusting it when I see it. Microsoft also describes that the rows are inserted randomly.
I need to take a random sample of 5000 rows for each of several types of things in one big table. If “INSERT TOP 5000” really will pull 5000 rows randomly, then I am all set. But if this is really just pulling 5000 rows from the table while an unknown sort order is applied, it will not work.
Can anyone confirm or deny that this is truely random (or at least as pseudorandom as computers can get)?
Thanks in advance.
@Pinal – But why does the second method ignores the “Oder By” clause. Is the “Select” statement not ordering the column ID in descending order or the it is but the “Insert” statement is again sorting it in ascending order and inserting the top 2 rows ?
Dear Sir ,
How i can Add the new column in any Old Existing Table at First,Middle and Last position by query.Please give me some proper query .
Thanks,
D K Dhiraj
i need help for my data mining project , i want to make a split row from my table into 2 new table(training,testing) .. how the sql statement move random row into new table from old table ??
i need a top 1-100 id’s in the table.What is the Sql Query