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.
|
| Author: DK 24 Jun 2009 | Member Level: Bronze Points : 0 |
Cool.
|
| Author: Sureshbabu 29 Jun 2009 | Member 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 2009 | Member Level: Silver 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 2009 | Member Level: Silver 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 2009 | Member Level: Diamond 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 2009 | Member Level: Diamond 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
|