Sql Server-Insert multiple records in table in single insert statement

Introduction: In this article I am going to share multiple ways to insert multiple rows in  sql sever table in one insert statement.


Description: Here I have mentioned 4 different ways to insert data in table. The traditional approach is time consuming and boring approach since we have to repeat same lengthy syntax again and again to insert each record in table. But I have mentioned some other ways to quickly insert multiple records at once.

Implementation: Let’s create a sample table “tbEmployee”  for demonstration purpose using the following script.
CREATE TABLE tbEmployee (EmpCode VARCHAR(10),EmpName VARCHAR(100), Age INT);

-- Method 1 - Traditional approach of multiple insert
INSERT INTO tbEmployee (EmpCode, EmpName,Age) VALUES (000001, 'Rahul',22);
INSERT INTO tbEmployee (EmpCode, EmpName,Age) VALUES (000002, 'Sonam',23);
INSERT INTO tbEmployee (EmpCode, EmpName,Age) VALUES (000003, 'Sahil',21);
INSERT INTO tbEmployee (EmpCode, EmpName,Age) VALUES (000004, 'Raunak',24);
INSERT INTO tbEmployee (EmpCode, EmpName,Age) VALUES (000005, 'Shikhir',21);

--Check inserted records
SELECT * FROM tbEmployee;

-- Clear all records from table
TRUNCATE TABLE tbEmployee;


-- Method 2 - Traditional but short approach of multiple insert
INSERT INTO tbEmployee VALUES (000001, 'Rahul',22);
INSERT INTO tbEmployee VALUES (000002, 'Sonam',23);
INSERT INTO tbEmployee VALUES (000003, 'Sahil',21);
INSERT INTO tbEmployee VALUES (000004, 'Raunak',24);
INSERT INTO tbEmployee VALUES (000005, 'Shikhir',21);

--Check inserted records
SELECT * FROM tbEmployee

-- Clear all records from table
TRUNCATE TABLE tbEmployee;


-- Method 3 - INSERT INTO-SELECT UNION APPROACH
INSERT INTO tbEmployee (EmpCode, EmpName,Age)
SELECT 000001, 'Rahul',22
UNION ALL
SELECT 000002, 'Sonam',23
UNION ALL
SELECT 000003, 'Sahil',21
UNION ALL
SELECT 000004, 'Raunak',24
UNION ALL
SELECT 000005, 'Shikhir',21;

--Check inserted records
SELECT * FROM tbEmployee;

-- Clear all records from table
TRUNCATE TABLE tbEmployee;


-- Method 4 - Row construction method for SQL Server 2008 and above version method
INSERT INTO tbEmployee (EmpCode, EmpName,Age)
VALUES (000001, 'Rahul',22), (000002, 'Sonam',23), (000003, 'Sahil',21), (000004, 'Raunak',24), (000005, 'Shikhir',21);

--Check inserted records
SELECT * FROM tbEmployee;

Output will be as: 
EmpCode
Name
Age
E000001
Rahul
22
E000002
Sonam
23
E000003
Sahil
21
E000004
Raunak
24
E000005
Shikhir
21

 Now over to you:
"A blog is nothing without reader's feedback and comments. So please provide your valuable feedback so that i can make this blog better and If you like my work; you can appreciate by leaving your comments, hitting Facebook like button, following on Google+, Twitter, Linkedin and Pinterest, stumbling my posts on stumble upon and subscribing for receiving free updates directly to your inbox . Stay tuned and stay connected for more technical updates."
Previous
Next Post »

5 comments

Click here for comments
Anonymous
admin
June 10, 2015 ×

Hi it's really good post for those who are starting sql

Reply
avatar
June 16, 2015 ×

Thanks for appreciating my efforts..stay connected for more useful updates.

Reply
avatar
July 21, 2015 × This comment has been removed by a blog administrator.
avatar
Unknown
admin
August 18, 2016 ×

Hi..
It is nice post
And every one it is useful.

Reply
avatar
September 09, 2016 ×

I am glad you found this article helpful..stay connected and keep reading for more updates.

Reply
avatar

If you have any question about any post, Feel free to ask.You can simply drop a comment below post or contact via Contact Us form. Your feedback and suggestions will be highly appreciated. Also try to leave comments from your account not from the anonymous account so that i can respond to you easily..