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)
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
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:
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?
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.