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.

In previous articles i explained Sql query to count department wise male,female and total employees and Split comma separated string into table rows and Difference between Temporary Table and Table Variable and Full Outer join example in sql server and Difference between Delete and Truncate

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."

• Tweet
• Share
• Share
• Share

Related Post