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

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 clause along with INSERT. It is very interesting to also observe the difference between both the methods. Let us take one real example and understand what exactly happens in either case.

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