Insert Multiple Rows in a Single Insert Statement in SQL Server

In this blog we will insert multiple rows in a single insert statement in SQL Server.

I will demonstrate two methods to achieve the same. Let us proceed to the first method now.

Method 1

Write the following script in SQL Server.

declare @Student table
(
   Studentidint,
   StudentNamevarchar(50),
   Marks int
)

insert into @Student (Studentid, StudentName, Marks)
values(1,'Nitin Tyagi',300),
(2,'Vijay Singh',200),
(3,'Sameer Verma',400),
(4,'Akash Dutt',300)

select * from @Student

We get the following output,



The values are inserted in the table. Let us now check the second method to do the same.

Method 2

Write the following script in SQL Server.

declare @Student table
(
   Studentidint,
   StudentNamevarchar(50),
   Marks int
)

insert into @Student (Studentid, StudentName, Marks)

select 1,'Anil',100
union all
select 2,'Sunil',200

select * from @Student

Execute the preceding script and check the output.



As we can see the records are inserted in the table.