How to insert multiple records with a single SQL query?

During development and testing of any application with a database, you need to have a huge set of test data. You can use any of the several methods to insert data to the tables, like the Bulk Insert method, Bulk Import using Bcp Utility, OPENROWSET, external applications, etc… However, here I have two basic methods you can use, if you want to insert multiple records with a single query. Let’s see them one by one.

Bothe the methods use the T-SQL Table Value Constructor. The table value constructor can be used in two different ways.

1. As the VALUES clause of an INSERT … VALUES statement.
2. As a derived table.

Using these methods, you can store the data along with the query in a .sql file. You just need to open the sql file in a SSMS and run it against the database and replenish the data. You don’t need another source table to store the data.

Let us see both these methods.

1. Using VALUES clause

In this table value constructor method, you can insert up to 1,000 records with a single query. This is just an INSERT INTO statement, where the values of each record under the VALUES clause are enclosed in a bracket and segregated by a comma. Here is an example:

CREATE TABLE MtbTable
(
	[Column_1] int,
	[Column_2] varchar(4),
	[Column_3] varchar(15),
	[Column_4] datetime
);
GO

INSERT INTO MtbTable
	( Column_1, Column_2, Column_3, Column_4 )
VALUES
	(1, 'AB01', 'AB AB BA', '2023-01-01'),
	(2, 'BC02', 'SOMETHING', '2023-02-02'),
	(3, 'CD03', 'SOMETHING ELSE', '2023-03-03'),
	(4, 'DE04', 'TD TD TD', '2023-04-04'),
	(5, 'EF05', 'YES YES', '2023-05-05'),
	(6, 'FG06', 'NO NO', '2023-06-06');
GO

SELECT * FROM MtbTable;
GO

2. Using derived table

Using this method, you can add more than 1,000 records to the table. Here is an example.

CREATE TABLE MtbTable
(
	[Column_1] int,
	[Column_2] varchar(4),
	[Column_3] varchar(15),
	[Column_4] datetime
);
GO

INSERT INTO MtbTable
	( Column_1, Column_2, Column_3, Column_4 )
SELECT
	Column_1, Column_2, Column_3, Column_4
FROM (
	VALUES
		(1, 'AB01', 'AB AB BA', '2023-01-01'),
		(2, 'BC02', 'SOMETHING', '2023-02-02'),
		(3, 'CD03', 'SOMETHING ELSE', '2023-03-03'),
		(4, 'DE04', 'TD TD TD', '2023-04-04'),
		(5, 'EF05', 'YES YES', '2023-05-05'),
		(6, 'FG06', 'NO NO', '2023-06-06')
) derived_tab(Column_1, Column_2, Column_3, Column_4);
GO

SELECT * FROM MtbTable;
GO

Reference

• More about Table Value Constructor at Microsoft Docs.