Insert Multiple records into a SQL-Server Database

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.
There are two 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 TEST_DB contains a table called Student with the following fields:


1. StdID
2. First Name
3. Last Name
4. Country
5. State
6. ZipCode


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


MULTIPLE INSERT STATEMENTS


USE Test_DB
GO


INSERT INTO Employee  (StdID, [FIRST NAME], [LAST NAME], [Country], [State],[ZIPCode])
        VALUES (1001, 'Student1','User1', 'INDIA','Bhopal','462001')


INSERT INTO Employee  (StdID, [FIRST NAME], [LAST NAME], [Country], [State],[ZIPCode])
        VALUES (1002, 'Student2','User2', 'INDIA','Bhopal','462001')


INSERT INTO Employee  (StdID, [FIRST NAME], [LAST NAME], [Country], [State],[ZIPCode])
        VALUES (1003, 'Student3','User3', 'INDIA','Banglore','560068')


INSERT INTO Employee  (StdID, [FIRST NAME], [LAST NAME], [Country], [State],[ZIPCode])
        VALUES (1004, 'Student4','User4', 'INDIA','Banglore','560068')


INSERT INTO Employee  (StdID, [FIRST NAME], [LAST NAME], [Country], [State],[ZIPCode])
        VALUES (1005, 'Student5','User5', 'INDIA','Banglore','560068')


GO


1). USING UNION ALL


The same result can be accomplished using the following Code.


USE Test_DB
GO


INSERT INTO Employee  (StdID, [FIRST NAME], [LAST NAME], [Country], [State],[ZIPCode])
SELECT 1001, 'Student1','User1', 'INDIA','Bhopal','462001'
UNION ALL
SELECT 1002, 'Student2','User2', 'INDIA','Bhopal','462001'
UNION ALL
SELECT 1003, 'Student3','User3', 'INDIA','Banglore','560068'
UNION ALL
SELECT 1004, 'Student4','User4', 'INDIA','Banglore','560068'
UNION ALL
SELECT 1005, 'Student5','User5', 'INDIA','Banglore','560068'


GO


2). USING SQL Server 2008 Row Construction Method


USE TEST_DB
GO


INSERT INTO Employee  (EmpID, [FIRST NAME], [LAST NAME], [Country], [State],[ZIPCode])
VALUES (1001, 'Student1','User1', 'INDIA','Bhopal','462001'),
(1002, 'Student2','User2', 'INDIA','Bhopal','462001'),
(1003, 'Student3','User3', 'INDIA','Banglore','560068'),
(1004, 'Student4','User4', 'INDIA','Banglore','560068'),
(1005, 'Student5','User5', 'INDIA','Banglore','560068')