During my recent training at one of the clients, I was asked about the enhancement in the TOP clause. When I demonstrated my script on how TOP works along with INSERT, one of the attendees suggested that I should also write about this script on my blog. So, today I want to share this script with all of you and hear your thoughts on it.

First, let’s understand that there are two different techniques to limit the insertion of rows into a table:

Method 1: INSERT INTO TABLE … SELECT TOP (N) Cols… FROM Table1

This method allows you to insert only the top N rows from a SELECT statement into a table. For example:

INSERT INTO Table2
SELECT TOP (5) Col1, Col2
FROM Table1;

Method 2: INSERT TOP (N) INTO TABLE … SELECT Cols… FROM Table1

This method is an enhancement to the TOP clause along with INSERT. It allows you to specify the number of rows to insert directly in the INSERT statement. For example:

INSERT TOP (5) INTO Table2 (Col1, Col2)
SELECT Col1, Col2
FROM Table1;

Now, let’s play with a real example to understand the difference between these two methods.

USE tempdb

-- Create Table
IF EXISTS (SELECT * FROM sys.objects WHERE OBJECT_ID = OBJECT_ID(N'TestValue') AND type IN (N'U'))
    DROP TABLE TestValue

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

-- Select Data from Table
SELECT * FROM TestValue

-- Create Two Tables 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

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

CREATE TABLE InsertTestValue1 (ID INT)

-- Option 1: Top with Select
INSERT INTO InsertTestValue (ID)
SELECT TOP (2) ID
FROM TestValue
ORDER BY ID DESC

-- Option 2: Top with Insert
INSERT TOP (2) INTO InsertTestValue1 (ID)
SELECT ID
FROM TestValue
ORDER BY ID DESC

-- Check the Data
SELECT * FROM InsertTestValue
SELECT * FROM InsertTestValue1

-- Clean up
DROP TABLE InsertTestValue
DROP TABLE InsertTestValue1
DROP TABLE TestValue

Now let’s check the result of the above SELECT statements.

When Option 2 is used, the ORDER BY clause is completely ignored and the data is inserted in any order. This is an interesting feature to note.

In future articles, we will talk about the performance of these queries. But for now, I would like to hear your thoughts on this feature. Have you used INSERT TOP(N) in your application?