SQL Server Insert Multiple Rows with One Statement

Introduction:

Here I will explain how to insert multiple rows with one insert statement in sql server or insert multiple multiple row values with stored procedure in sql server or insert multiple rows from select statement in sql server or insert multiple rows into table at once in sql server or sql server insert multiple rows from another table 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 or select statement in sql server.

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

SQL Server Insert Multiple Rows At Once

Following is the syntax of inserting multiple rows with one statement in sql server.

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

SQL Server Insert Multiple Rows from Select Statement

Following is the syntax of inserting multiple rows into table using select statement in sql server.

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