SQL SERVER – INSERT TOP (N) INTO Table – Using Top with INSERT

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.

Insert alogn with Top (N)

Insert alogn with Top (N)

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 (http://blog.SQLAuthority.com)

About these ads

25 thoughts on “SQL SERVER – INSERT TOP (N) INTO Table – Using Top with INSERT

  1. Pingback: SQL SERVER – Performance Comparison – INSERT TOP (N) INTO Table – Using Top with INSERT Journey to SQL Authority with Pinal Dave

  2. 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.

  3. 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

    • 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.

  4. 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.

  5. 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.

  6. 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!

  7. 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.

  8. @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 ?

  9. 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

  10. 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 ??

  11. Pingback: SQL SERVER – Weekly Series – Memory Lane – #018 | SQL Server Journey with SQL Authority

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s