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.
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?
Reference: Pinal Dave (https://blog.sqlauthority.com)
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