.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
Gaurav Pal

Home >> Articles >> DataBase >> Post New Resource Bookmark and Share   

 Subscribe to Articles

Insert Multiple Records Using Single Insert - SQL SERVER

Posted By:Gaurav Arora       Posted Date: April 26, 2009    Points: 25    Category: DataBase    URL: http://www.dotnetspark.com  

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)


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


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)

 Subscribe to Articles


Further Readings:


No response found. Be the first to respond this post

Post Comment

You must Sign In To post reply
Find More Articles on C#, ASP.Net, Vb.Net, SQL Server and more Here

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend