SQL SERVER-INSERT MULTIPLE RECORDS IN TABLE IN SINGLE 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