Skip to main content
Inserting Multiple Rows at Once in SQL Server

Inserting Multiple Rows at Once in SQL Server

One of the common query we have always received from developers is that if there is any way to insert multiple rows into a single table in a single statement? To insert multiple rows into table with single insert query or select statement in sql server we can follow different methods those are listed in the post.

Inserting Multiple Rows at Once in SQL Server #multipleRows #sqlserver #database Click To Tweet

In the following post we have demonstrated two different methods to insert multiple values into a single table.

Insert Multiple Rows At Once – SQL Server

The syntax of inserting multiple rows with one statement in sql server is as shown below:

INSERT INTO tablename(column1,column2,column3)
VALUES(value1,value2,value3)
      ,(value4,value5,value6)
      ,(value7,value8,value9)

Example:

CREATE TABLE blog
(
 Id INT
 ,Name VARCHAR(20)
 ,Category VARCHAR(20)
)

INSERT INTO blog(Id,Name,Category)
VALUES (1,'dotnetcrunch','ASP.NET') 
       ,(2,'sqlauthority','SQL SERVER') 
       ,(3,'wcftutorial','WCF')

Insert Multiple Rows from Select Statement – SQL Server

The syntax for inserting multiple rows into table using select statement in sql server in as follows:

INSERT INTO tablename(column1,column2,column3)
SELECT value1,value2,value3
UNION ALL
SELECT value4,value5,value6
UNION ALL
SELECT value7,value8,value9

Example:

CREATE TABLE blog
(
 Id INT
 ,Name VARCHAR(20)
 ,Category VARCHAR(20)
)

INSERT INTO blog(Id,Name,Category)
SELECT 1,'dotnetcrunch','ASP.NET'
UNION ALL
SELECT 2,'sqlauthority','SQL SERVER'
UNION ALL
SELECT 3,'wcftutorial','WCF'

SELECT * FROM blog

When we run above query it will return all inserted records with single query like as shown below:

SQL Server Insert Multiple Rows from Select Statement

Now, no need to write multiple insert statements for inserting the data. It will not only save your time but also it will help you to get rid of the redundant insert statements.

Please note: The insert multiple rows at once would be an atomic operation, either all rows would be inserted or none of them.

Hope you were able to follow the post on Inserting Multiple Rows at Once in SQL Server.

What do you think?

Dear Readers,
If you have any questions or suggestions please feel free to email us or put your thoughts as comments below. We would love to hear from you. If you found this post or article useful then please share along with your friends and help them to learn.

Happy Crunching!

Was this article worth reading? Share it with fellow developers too.

DotNetCrunch

DotNetCrunch is a popular blog for latest Microsoft® technologies which is aimed for beginners and intermediate level professionals.

25