How to Insert multiple rows in a single SQL query

The insert statement insert one or more records into a database table.


There are multiple ways to insert multiple rows into Sql Server. For example, the most known way consist in inserting records consist in on the following way.

Insert into MyTable(Row 1, Row 2...Row N) Values(Value 1, Value 2,... ,Value N);
Insert into MyTable(Row 1, Row 2...Row N) Values(Value 3, Value 4,... ,Value N);
Insert into MyTable(Row 1, Row 2...Row N) Values(Value 4, Value 5,... ,Value N);

We are going to create a table called users for working on the following examples.

Example

For the following examples on this article we are going to create a table called users and truncate it each time we use it.

Create Table users(
username varchar(250)
,department varchar(250)
)

On this example we are going to insert 5 records into our users table.

INSERT INTO USERS(username,department) VALUES('USER1','DEPARTMENT1');
INSERT INTO USERS(username,department) VALUES('USER2','DEPARTMENT2');
INSERT INTO USERS(username,department) VALUES('USER3','DEPARTMENT3');
INSERT INTO USERS(username,department) VALUES('USER4','DEPARTMENT4');
INSERT INTO USERS(username,department) VALUES('USER5','DEPARTMENT5');

Results

The standard Sql Server Insert requires you to insert values row by row.

If you require to insert more than 1 value at the time you would have to write multiple sql server statements.

1. Inserting multiple rows using a single sql insert statement

There are different ways to insert multiple columns. The most well known way is by using insert followed by values that you want to insert separated by parentheses and commas.

INSERT INTO MyTable ( Column 1, Column 2, ..., Column n) VALUES
( Value 1, Value 2,  ..., Value n )
, ( Value 1, Value 2, ..., Value n)

Example

INSERT INTO USERS(username,department) 
VALUES('USER1','DEPARTMENT1')
	 ,('USER2','DEPARTMENT2')
	 ,('USER3','DEPARTMENT3')
	 ,('USER4','DEPARTMENT4')
	 ,('USER5','DEPARTMENT5');

Results

As you can see this gives us the same result than inserting the same data in 5 different statements.

In addition, some restrictions related with this method are related with the maximum number of rows in VALUES clause that must be less than 1000.

This is not related with INSERT statement itself so don’t worry about a restriction for inserting more than 1000 values in a table.

If you want to insert 2000 values using this method you will need to separate this task on two INSERT statements, each one containing 1000 rows.

2. Inserting multiple rows using select and union all

Something interesting about Insert statement is that you can insert records into a table by combining the insert statement and results from a select statement that has the same number of columns.

For using this method you can check the following syntax

Insert into MyTable(Row 1, Row 2…Row N) 
Select Value 1, Value 2,… ,Value N
UNION ALL
Select Value 3, Value 4,… ,Value N
UNION ALL
Select Value 5, Value 6,… ,Value N

This syntax allows us to insert multiple values using a single sql statement.

We are using UNION ALL instead of using UNION to avoid getting duplicated rows not inserted in the table.

Example

INSERT INTO USERS(username,department) 
SELECT 'USER1','DEPARTMENT1' UNION ALL
SELECT 'USER1','DEPARTMENT2' UNION ALL
SELECT 'USER1','DEPARTMENT3' UNION ALL
SELECT 'USER1','DEPARTMENT4' UNION ALL
SELECT 'USER1','DEPARTMENT5'

Results

This method does not have any kind of restriction, so you can insert as many rows as you want.

3. Conclusion

This methods work on the same way and return the same results but inserting values in a single sentence by using VALUES method improves performance.


Leave a Reply

Your email address will not be published. Required fields are marked *