Multi-Row INSERT in SQL Server: One Statement, All or Nothing

A multi-row INSERT adds several rows with one statement, and the statement succeeds or fails as a whole. SQL Server has supported it since version 2008. You write one list of value groups, separated by commas, instead of one INSERT per row.

The Sample Table

A small table of books is enough. Run this in any test database. Every result in this post comes from SQL Server 2025.

CREATE TABLE Books (BookID INT PRIMARY KEY, Title VARCHAR(40) NOT NULL, Pages INT NULL);
GO

One Row at a Time

The first insert is the usual form. Then two separate statements add two more rows, and each reports one row affected.

INSERT INTO Books (BookID, Title, Pages) VALUES (1, 'Tea Garden Notes', 96);

INSERT INTO Books (BookID, Title, Pages) VALUES (2, 'Quiet Mornings', 75);

INSERT INTO Books (BookID, Title, Pages) VALUES (3, 'Small Rivers', 75);

The message says (1 rows affected) twice. Now the same idea with one statement. The comma between the value groups is the only new part.

INSERT INTO Books (BookID, Title, Pages)
VALUES (4, 'Paper Boats', 110), (5, 'Slow Trains', 88);

This time the message says (2 rows affected). The table now holds five books, and both new rows arrived together. A SELECT confirms it.

SELECT BookID, Title, Pages FROM Books ORDER BY BookID;

@@ROWCOUNT reports the same number right after the insert. This statement adds three rows, and the second row has a NULL page count, which the Pages column allows.

INSERT INTO Books (BookID, Title, Pages)
VALUES (6, 'Window Seat', 64), (7, 'Rainy Day Maps', NULL), (8, 'Hill Country', 120);
SELECT @@ROWCOUNT AS RowsInserted;

All or Nothing

Failure shows the real difference. Book 4 already exists, so any insert that repeats it breaks the primary key. This multi-row INSERT has a good row, the bad one, and another good row.

INSERT INTO Books (BookID, Title, Pages)
VALUES (9, 'Lantern Walk', 70), (4, 'Duplicate Key', 50), (10, 'Last Light', 45);

SELECT COUNT(*) AS BooksNow FROM Books;

SQL Server answers with Msg 2627, a duplicate key. The count is still 8, so the good row with book 9 was not saved either. A transaction log check agrees: two separate INSERT statements wrote two transactions, and one multi-row INSERT wrote one.

That rule is useful. Rows that belong together, such as an order and its lines, arrive together or not at all.

Two separate statements behave differently. The first one saves its row, and only the second fails.

INSERT INTO Books (BookID, Title, Pages) VALUES (11, 'First Of Two', 70);

INSERT INTO Books (BookID, Title, Pages) VALUES (4, 'Duplicate Key', 50);

SELECT COUNT(*) AS BooksNow FROM Books;

The count is now 9. Book 11 stayed. Pick the form that matches what you want: all rows or none, or each row on its own.

Other Ways It Fails

A NULL for a column that doesn’t allow one has the same effect as a duplicate key. The whole statement fails, including the good row.

INSERT INTO Books (BookID, Title, Pages)
VALUES (12, 'Has A Title', 30), (13, NULL, 40);

That returns Msg 515. Every value group must have the same number of values. A short row is rejected with Msg 10709.

INSERT INTO Books (BookID, Title, Pages)
VALUES (14, 'Short Row', 30), (15, 'Missing Pages');

Too many values for the table gives Msg 213. Here the insert lists four values for a table with three columns. Listing the column names, as every example here does, makes this mistake easier to see.

INSERT INTO Books VALUES (16, 'Too Many Values', 30, 'R');

The 1,000 Row Limit

One statement can hold up to 1,000 value groups. A statement with 1,000 rows worked and reported (1000 rows affected). A statement with 1,001 rows failed with Msg 10738, which names the maximum of 1000 row values. For larger loads, split the work into batches of 1,000 or fewer.

VALUES Without a Table

The same list can act as a table in a query. Name the columns after it, and you have a small lookup list with no table behind it.

SELECT v.Genre, v.Shelf
FROM (VALUES ('Travel', 'A1'), ('Poetry', 'B4'), ('Cooking', 'C2')) AS v (Genre, Shelf);

Clean Up

Remove the sample table when you finish.

DROP TABLE Books;

What to Remember

Use a multi-row INSERT when the rows belong together. One bad row undoes the whole statement. Keep each statement to 1,000 rows or fewer, and give every row the same number of values.

To go further, read Creating Dataset Using VALUES Clause Without Creating A Table, Insert Multiple Rows in SQL Server: SQL in Sixty Seconds #024 and How to Insert Multiple Rows in a Single SQL Query.

A multi-row INSERT is not a shortcut for typing, it is a promise that the rows travel together.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.