SQL Server - Insert Multiple Rows with One Insert Statement

Introduction:

Here I will explain how to insert multiple rows into table with single insert query in sql server or sql server insert multiple records in table with one insert statement with example.

Description:

In previous articles I explained SQL Server Get total rows count in union query, SQL Server Difference between Union and Union All, SQL Server Replace multiple spaces in string with single space, SQL Server Remove html tags from string and many articles relating to SQL server. Now I will explain how to insert multiple rows into table with single insert query in sql server.

To insert multiple rows into table with single insert query in sql server we can follow different methods like as shown below

Method1:

insert into @table1(id,name,education) values(val1,val2,val3), (val4,val5,val6), (val7,val8,val9)

Example:

declare @table1 table(id int,name varchar(50),education varchar(50)) insert into @table1(id,name,education) values(1,'suresh','b.tech'), (2,'rohini','msc'), (3,'praveen','btech') select * from @table1

Method2:

insert into @table1(id,name,education) select val1,val2,val3 union all select val4,val5,val6 union all select val7,val8,val9

Example:

declare @table1 table(id int,name varchar(50),education varchar(50)) insert into @table1(id,name,education) select 1,'suresh','b.tech' union all select 2,'rohini','msc' union all select 3,'praveen','btech' select * from @table1

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

Output

If you enjoyed this post, please support the blog below. It's FREE! Get the latest Asp.net, C#.net, VB.NET, jQuery, Plugins & Code Snippets for FREE by subscribing to our Facebook, Twitter, RSS feed, or by email.

Subscribe by RSS Subscribe by Email