How to insert Multiple Rows with One Insert Statement in SQL Server

Introduction:

Sometime while working with SQL Server database it is needed to insert multiple value by executing single insert query.

In this article I will explain how to insert multiple rows into table with single insert query in SQL Server.

There are multiple methods are there to insert multiple rows into table with single insert query in SQL Server:

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,'Anil','MBA'),

(2,'Anish','PHD'),

(3,'Ansh','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, 'Anil','MBA'

union all

select 2, 'Anish','PHD'

union all

select 3, 'Ansh','btech'

select * from @table1