Insert multiple records into a SQL Server table using one INSERT statement

Common way of inserting multiple records in the table following using T-SQL is using multiple INSERT statements:

USE Database_name
GO
INSERT INTO Table_name (Column1, Column2)
VALUES ('A',1);
INSERT INTO Table_name (Column1, Column2)
VALUES ('B',2);
INSERT INTO Table_name (Column1, Column2)
VALUES ('C',3);
INSERT INTO Table_name (Column1, Column2)
VALUES ('D',4);
GO

Another way is to use multiple UNION ALL, INSERT INTO and SELECT clauses:

USE Database_name
GO
INSERT INTO Table_name (Column1, Column2)
SELECT 'A' ,1
UNION ALL
SELECT 'B' ,2
UNION ALL
SELECT 'C' ,3
UNION ALL
SELECT 'D' ,4
GO

SQL Server 2008 and SQL Server 2012 provide a new method (Table Value Constructor) to insert data to SQL Server tables:

USE Database_name
GO
INSERT INTO Table_name (Column1, Column2)
VALUES ('A',1),
('B',2),
('C',3),
('D',4)

Limitation of this method is the maximum number of 1000 rows that can be inserted. If you try to insert more than 1000 rows at a time, you will get the following error message:

Msg 10738, Level 15, State 1, Line 2
The number of row value expressions in the INSERT statement exceeds the maximum allowed number of 1000 row values.

Break the INSERT statement into multiple INSERT statements, each containing no more than 1000 rows to insert.

If the post helped you, please share it: