w3schools - sql insert multiple rows from select
Inserting multiple rows in a single SQL query? (3)
This question already has an answer here:
I have multiple set of data to insert at once, say 4 rows.
My table has three columns:
INSERT INTO MyTable VALUES ("John", 123, "Lloyds Office"); INSERT INTO MyTable VALUES ("Jane", 124, "Lloyds Office"); INSERT INTO MyTable VALUES ("Billy", 125, "London Office"); INSERT INTO MyTable VALUES ("Miranda", 126, "Bristol Office");
insert all 4 rows in a single
INSERT statements that use
VALUES syntax can insert multiple rows. To do this, include multiple lists of column values, each enclosed within parentheses and separated by commas.
INSERT INTO tbl_name (a,b,c) VALUES(1,2,3),(4,5,6),(7,8,9);
NOTE: This answer is for SQL Server 2005. For SQL Server 2008 and later, there are much better methods as seen in the other answers.
You can use INSERT with SELECT UNION ALL:
INSERT INTO MyTable (FirstCol, SecondCol) SELECT 'First' ,1 UNION ALL SELECT 'Second' ,2 UNION ALL SELECT 'Third' ,3 ...
Only for small datasets though, which should be fine for your 4 records.
In SQL Server 2008 you can insert multiple rows using a single SQL INSERT statement.
INSERT INTO MyTable ( Column1, Column2 ) VALUES ( Value1, Value2 ), ( Value1, Value2 )
For reference to this have a look at MOC Course 2778A - Writing SQL Queries in SQL Server 2008.
INSERT INTO MyTable ( Column1, Column2, Column3 ) VALUES ('John', 123, 'Lloyds Office'), ('Jane', 124, 'Lloyds Office'), ('Billy', 125, 'London Office'), ('Miranda', 126, 'Bristol Office');