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.

  1. declare @Student table  
  2. (  
  3.    Studentidint,  
  4.    StudentNamevarchar(50),  
  5.    Marks int  
  6. )  
  7.   
  8. insert into @Student (Studentid, StudentName, Marks)  
  9. values(1,'Nitin Tyagi',300),  
  10. (2,'Vijay Singh',200),  
  11. (3,'Sameer Verma',400),  
  12. (4,'Akash Dutt',300)  
  13.   
  14. 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.
  1. declare @Student table  
  2. (  
  3.    Studentidint,  
  4.    StudentNamevarchar(50),  
  5.    Marks int  
  6. )  
  7.   
  8. insert into @Student (Studentid, StudentName, Marks)  
  9.   
  10. select 1,'Anil',100  
  11. union all  
  12. select 2,'Sunil',200  
  13.   
  14. select * from @Student  
Execute the preceding script and check the output.



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