SQL Insert Multiple Rows

If you regularly need to add lots of new data to a SQL table, manually inserting each row can be slow and repetitive. One of SQL’s great strengths is it lets you insert multiple rows in one go.

In this guide, we’ll walk you through:

• How to insert single and multiple rows

• How to return IDs for newly inserted rows

• How to insert rows when using a custom column order

Meanwhile, if you’re eager to learn more about SQL, try one of our SQL training courses, from introduction to advanced.

Creating Dummy Table

The following script creates a dummy database with a single table. You will be using this table for inserting a single or multiple rows of data.

CREATE DATABASE Sales

GO
USE Sales

CREATE Table Product (
ProductID INT PRIMARY KEY IDENTITY (1, 1),
ProductName VARCHAR(255),
ProductPrice FLOAT,
ProductCategory VARCHAR(255),
ProductQuantity INT
);

At the moment, the table is empty, as evident from the output of the following SELECT query. 

SELECT FROM Product

Inserting A Single Row

The basic syntax for adding a single row is:

INSERT INTO Product
VALUES ('Laptop', 1250.50, 'Computers', 25);

This inserts a single product into the Product table. Just make sure the values match the column order in the table.

You can confirm it worked with:

SELECT * FROM Product;

If you would like to learn how to automatically update the data of multiple rows in a table, check out our guide about the UPDATE statement.

Inserting Multiple Rows – INSERT Statements

 You can execute multiple single INSERT statements together as shown in the following script:

INSERT INTO Product
VALUES
('Chair', 11.00, 'Furniture', 10),
('LED TV', 450.20, 'Electronics', 2),
('Desktop Computer', 700.2, 'Computers', 2)

In the output, you will see that essentially SQL Server treats the above three statements as single statements. 

The records are inserted successfully and if you execute the SELECT statement, your Product table now looks like this.

Inserting Multiple Rows With One Statement

The correct way to insert multiple records in a table in SQL server is through the following syntax. Here, you can see that after the VALUES keyword, multiple records are specified inside parentheses separated by commas. 

INSERT INTO Product
VALUES
('Chair', 11.00, 'Furniture', 10),
('LED TV', 450.20, 'Electronics', 2),
('Desktop Computer', 700.2, 'Computers', 2)

If you execute the above script, you will see the following output. You can see that in this case three rows are affected at once, instead of a single row affected three times as you saw in the output of the previous script. 

You now have the following records in your table. 

If for some reason you need to add a temporary table to your SQL database, you can learn about that here too!

Returning Inserted IDs

You can also return the inserted ID list while inserting multiple records in an SQL Server table. The syntax remains the same as the previous script, with one addition. You have to add the OUTPUT keyword followed by inserted.ID_Column_Name, before the VALUES keyword.

In our dummy dataset, the ID column for the product table is ProductID, hence we use the syntax “OUTPUT inserted.ProductID” to retrieve the IDs for the inserted records.

INSERT INTO Product

OUTPUT inserted.ProductID

VALUES
('Bed', 500.0, 'Furniture', 2),
('Car', 3500.5, 'Vehicle', 1),
('Pizza', 7.0, 'Food', 1)
In the output of the above script, you can see the inserted IDs i.e 8, 9, and 10.

The SELECT * query at this point should return the following records from the Product table.

Final Thoughts

If you only have one record to insert, go ahead and use a single INSERT.

But when you’re working with multiple records, using a single statement to insert them all at once is faster, easier, and more efficient.

Looking to deepen your SQL knowledge?