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