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.

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)