Saturday, June 27, 2009

SQL: INSERT Multiple records using One INSERT INTO Statement

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. ZipCodeTo 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')

GO


No comments:

Post a Comment