RSS Feed

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