How to Insert Multiple Records Using Single Insert - SQL SERVER

This is very interesting question, I have received from one of my colleague - Neeraj Tomar. How can I insert multiple values in table using only one insert? .

To insert values in a table, there are many ways like :

Use HrnPayroll --Change database name with yours

--Here you can try with any table available under above chosen database

INSERT INTO dbo.employees VALUES('0001', 'Gaurav','Arora',38)
INSERT INTO dbo.employees VALUES('0005', 'Shuby','Arora',28)
INSERT INTO dbo.employees VALUES('0007', 'Shweta','Arora',29)

Go

With the help of above lines, one can achieve the task but think for numerous insert statements to do the same one should repeat the Insert multiple times.

--One can achieve the multiple insertion with the following statement:
Insert Into dbo.employees (ID, FirstName, LastName, Age)
Select '0008','Arun', 'Kumar',39
Union All
Select '0009','Vibha', 'Arora',19
Union All
Select '0018','Neeraj', 'Tomar',23
Union All
Select '0118','Laxmi', 'Farswan',24

Go

With the help of above line one can insert multiple data using single Insert statement. The above both statements are working fine when using SQLSerevr 2000/2005.

The SQLServer2008 provides more stuff to add multiple values using single Insert statement.


--The following querry will happen only with SQLServer2008:
Insert Into dbo.employees (ID, FirstName, LastName, Age)
Values('1018','Neeraj', 'Shivasam',18)
Values('1118','Neeraj', 'Huda',38)
Values('1028','Gaurav', 'Malhotra',30)
Values('1128','Abhishek', 'Prasad',30)
Values('2128','Pankaj', 'Nautiyal',36)
Values('3128','Ritesh', 'Kashyap',33)