Resources » .NET programming » Databases

SQL : INSERT Multiple records using One INSERT INTO Statement


Posted Date: 21-Jun-2009  Last Updated:   Category: Databases    
Author: Member Level: Gold    Points: 10



To insert multiple records into a SQL Server database, we need to call the INSERT INTO query every time.

Suppose if we want to insert 10 records, we need to call INSERT INTO statement 10 times.

As every time we invoke the INSERT INTO statement the INSERT INTO statements are repeated multiple times.


The work around is to use one Single INSERT INTO statement in SQL Server.

Methods Available to Insert Multiple Records in Single INSERT INTO Statement

1. Using UNION ALL Keyword

2. SQL Server 2008 Row Construction Method

Assume the database TESTDB contains a table called Employee with the following fields:

1. EmpID

2. First Name

3. Last Name

4. Country

5. State

6. ZipCode


To Insert 6 records we have to call INSERT INTO 10 times as follows:

MULTIPLE INSERT STATEMENTS



USE TestDB
GO

INSERT INTO Employee (EmpID, [FIRST NAME], [LAST NAME], [Country], [State],[ZIPCode])
VALUES (1001, 'Benny','Roger', 'USA','NewYork','44072')

INSERT INTO Employee (EmpID, [FIRST NAME], [LAST NAME], [Country], [State],[ZIPCode])
VALUES (1002, 'Catherine','Maria', 'USA','NewYork','44072')

INSERT INTO Employee (EmpID, [FIRST NAME], [LAST NAME], [Country], [State],[ZIPCode])
VALUES (1003, 'Dany','Michael', 'USA','NewYork','44072')
INSERT INTO Employee (EmpID, [FIRST NAME], [LAST NAME], [Country], [State],[ZIPCode])
VALUES (1004, 'Eliza','Rodar', 'USA','NewYork','44072')

INSERT INTO Employee (EmpID, [FIRST NAME], [LAST NAME], [Country], [State],[ZIPCode])
VALUES (1005, 'Frank','Bennet', 'USA','NewYork','44072')

INSERT INTO Employee (EmpID, [FIRST NAME], [LAST NAME], [Country], [State],[ZIPCode])
VALUES (1006, 'Graham','Bell', 'USA','NewYork','44072')

GO



USING UNION ALL

The same result can be accomplished using the following Code.



USE TestDB
GO

INSERT INTO Employee (EmpID, [FIRST NAME], [LAST NAME], [Country], [State],[ZIPCode])
SELECT 1001, 'Benny','Roger', 'USA','NewYork','44072'
UNION ALL
SELECT 1002, 'Catherine','Maria', 'USA','NewYork','44072'
UNION ALL
SELECT 1003, 'Dany','Michael', 'USA','NewYork','44072'
UNION ALL
SELECT 1004, 'Eliza','Rodar', 'USA','NewYork','44072'
UNION ALL
SELECT 1005, 'Frank','Bennet', 'USA','NewYork','44072'
UNION ALL
SELECT 1006, 'Graham','Bell', 'USA','NewYork','44072'

GO



USING SQL Server 2008 Row Construction Method



USE TESTDB
GO

INSERT INTO Employee (EmpID, [FIRST NAME], [LAST NAME], [Country], [State],[ZIPCode])VALUES (1001, 'Benny','Roger', 'USA','NewYork','44072'),
(1002, 'Catherine','Maria', 'USA','NewYork','44072'),
(1003, 'Dany','Michael', 'USA','NewYork','44072'),
(1004, 'Eliza','Rodar', 'USA','NewYork','44072'),
(1005, 'Frank','Bennet', 'USA','NewYork','44072'),
(1006, 'Graham','Bell', 'USA','NewYork','44072')





Regarding Performance there is not much difference, but effectively the result is same.


Did you like this resource? Share it with your friends and show your love!

Responses to "SQL : INSERT Multiple records using One INSERT INTO Statement"
Author: DK    24 Jun 2009Member Level: Bronze   Points : 0
Cool.


Author: Sureshbabu    29 Jun 2009Member Level: Silver   Points : 2
Hi,

Iam Suresh, a member of this forum. Your answer was very much useful and I learnt something new from your answer. Thank you very much.

I need a favour from you.

Can you please provide me the links to download MS SQL server. Since Iam using vs 2008 and tried to install sql server 2005 developer edition, express edition and standard edition.

But in none of the editions i got management studio, except configuration tools option.

So, please provide me the exact link for downloading the sql server for VS 2008/2005 to work with.



Author: Prasoon Kumar    29 Jun 2009Member Level: Gold   Points : 2
Hi
Viji,

Thank you so much.

Though this query is not very complicated, it has

helped us to reduce our time effort as we need to give

different inputs to the testing team from our end for

different scenarios after truncating tables.

Now we can achieve the same, without lot of efforts.



Author: Prasoon Kumar    29 Jun 2009Member Level: Gold   Points : 2
Hi
Viji,

Thank you so much.

Though this query is not very complicated, it has

helped us to reduce our time effort as we need to give

different inputs to the testing team from our end for

different scenarios after truncating tables.

Now we can achieve the same, without lot of efforts.



Author: Viji RAJKUMAR    29 Jun 2009Member Level: Gold   Points : 1
Hi Prasoon,

Thanks for your compliments.

If you found my resource useful it would be great, if you can rate the same.

Regards,

Viji Rajkumar

VIJI RAJKUMAR
http://vijirajkumar.blogspot.com/



Author: Viji RAJKUMAR    29 Jun 2009Member Level: Gold   Points : 2
Hi Suresh,

Can you try this link

http://www.microsoft.com/downloadS/details.aspx?familyid=220549B5-0B07-4448-8848-DCC397514B41&displaylang=en

This is the URL to download Microsoft sql server express edition.


You can download

SQLEXPR.EXE

ReadmeSQLEXP2005.htm

RequirementsSQLEXP2005.htm

If you found this helpful rate my answer.

Regards

Viji Rajkumar
http://vijirajkumar.blogspot.com





Feedbacks      

Post Comment:




  • Do not include your name, "with regards" etc in the comment. Write detailed comment, relevant to the topic.
  • No HTML formatting and links to other web sites are allowed.
  • This is a strictly moderated site. Absolutely no spam allowed.
  • Name:   Sign In to fill automatically.
    Email: (Will not be published, but required to validate comment)



    Type the numbers and letters shown on the left.


    Submit Article     Return to Article Index

    Subscribe to Subscribers
    Active Members
    TodayLast 7 Daysmore...

    Awards & Gifts
    Talk to Webmaster Tony John

    Online Members

    Priya jain
    More...
    Copyright © SpiderWorks Technologies Pvt Ltd., Kochi, India