SQL SERVER – Insert Multiple Records Using One Insert Statement

How can we insert multiple values in table using only one insert? 

When there are multiple records are to be inserted in the table following is the common way using T-SQL.

 

INSERT INTO MyTable  (FirstCol, SecondCol)

        VALUES ('First',1);

INSERT INTO MyTable  (FirstCol, SecondCol)

        VALUES ('Second',2);

INSERT INTO MyTable  (FirstCol, SecondCol)

        VALUES ('Third',3);

INSERT INTO MyTable  (FirstCol, SecondCol)

        VALUES ('Fourth',4);

INSERT INTO MyTable  (FirstCol, SecondCol)

        VALUES ('Fifth',5);

GO

The clause INSERT INTO is repeated multiple times. Many times DBA copy and paste it to save time. There is another alternative to this, using UNION ALL and INSERT INTO … SELECT… clauses. 

Regarding performance there is not much difference. Also, insert script is used one time, so performance does not make much difference.

INSERT INTO MyTable (FirstCol, SecondCol)
SELECT 'First' ,1
UNION ALL
SELECT 'Second' ,2
UNION ALL
SELECT 'Third' ,3
UNION ALL
SELECT 'Fourth' ,4
UNION ALL
SELECT 'Fifth' ,5
GO

The effective result is same.

If one is using SQL Server 2008, then a new feature to insert multiple rows in SQL with using only one SELECT statement can be used.

SQL Server 2008 Method of Row Construction:
 
INSERT INTO MyTable (FirstCol, SecondCol)
VALUES ('First',1),
('Second',2),
('Third',3),
('Fourth',4),
('Fifth',5)