Summary: in this tutorial, you will learn how to insert multiple rows into a table using a single SQL Server
To add multiple rows to a table at once, you use the following form of the
INSERT INTO table_name (column_list)
In this syntax, instead of using a single list of values, you use multiple comma-separated lists of values for insertion.
The number of rows that you can insert at a time is 1,000 rows using this form of the
INSERT statement. If you want to insert more rows than that, then you should consider using multiple
BULK INSERT, or a derived table.
Note that this
INSERT multiple rows syntax is only supported in SQL Server 2008 or later.
INSERT multiple rows example
We will use the
promotions table created in the previous tutorial for the demonstration.
If you have not yet created the
promotions table, you can use the following
CREATE TABLE statement:
CREATE TABLE sales.promotions (
promotion_id INT PRIMARY KEY IDENTITY (1, 1),
promotion_name VARCHAR (255) NOT NULL,
discount NUMERIC (3, 2) DEFAULT 0,
start_date DATE NOT NULL,
expired_date DATE NOT NULL
The following statement adds multiple rows to the
INSERT INTO sales.promotions (
'2019 Summer Promotion',
'2019 Fall Promotion',
'2019 Winter Promotion',
SQL server issued the following message indicating that three rows have been inserted successfully.
(3 rows affected)
Let’s verify the insert by executing the following query:
Here is the output:
In this tutorial, you have learned how to use another form of the SQL Server
INSERT statement to insert multiple rows into a table using one