SQL Server - Insert multiple rows in a table

SQL Server - Insert multiple rows in a table

Lets create a new table called my_EMP.

CREATE TABLE my_EMP
(
empid int,
lastname varchar(255),
firstname varchar(255),
);
 

Now that we have created a table, lets insert values into it.

You already know this below method of inserting records in the table. Here you are repeating the insert statement.

Method: 1

INSERT INTO my_EMP
VALUES (001,'aaa','aaa');
INSERT INTO my_EMP
VALUES (002,'bbb','bbb');
INSERT INTO my_EMP
VALUES (003,'ccc','ccc');

Lets see the table now:

Select * from my_EMP

empid	lastname	firstname
1	aaa	aaa
2	bbb	bbb
3	ccc	ccc

Lets truncate the table.

Truncate table my_EMP

Below is another method which you can use to insert multiple rows in a table:

Method: 2

INSERT INTO my_EMP ( empid, lastname, firstname ) VALUES
(001,'aaa','aaa'),
(002,'bbb','bbb'),
(003,'ccc','ccc')
 

Lets see the table:

Select * from my_EMP

empid	lastname	firstname
1	aaa	aaa
2	bbb	bbb
3	ccc	ccc

The above method 2 has one limitation. You can insert maximum of 1000 rows with this method. If you try to insert 1001 rows, you will receive the below error message:

The number of row value expressions in the INSERT statement exceeds the maximum allowed number of 1000 row values.

Lets truncate the table.
Truncate table my_EMP

Below is third method to insert multiple rows in a table:

Method: 3

INSERT INTO my_EMP (empid, lastname, firstname)
SELECT 001,'aaa','aaa'
UNION ALL
SELECT 002,'bbb','bbb'
UNION ALL
SELECT 003,'ccc','ccc'

Lets see the table:

select * from my_EMP

empid	lastname	firstname
1	aaa	aaa
2	bbb	bbb
3	ccc	ccc