sql server – Inserting multiple rows in a single SQL query?

The Question :

1729 people think this question is useful

I have multiple set of data to insert at once, say 4 rows. My table has three columns: Person, Id and Office.

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");

Can I insert all 4 rows in a single SQL statement?

The Question Comments :
  • Moderator Note: Please take all discussion about the merits of this question to this meta post.
  • For oracle sql see stackoverflow.com/a/93724/1452172
  • @Chinggis6 Yes, trivially possible, just use a select for the column values:
  • @Chinggis6 insert into profiles (name, description) select first, 'Auto-generated' from users You seem to be confusing insert and update statement, which are different beasts.
  • @Chinggis6 Ah I see. Well, I just recommend using standard insert ... select syntax, it’ll get you everything you need and is as flexible as can be wished for. dev.mysql.com/doc/refman/5.5/en/insert.html

The Answer 1

2293 people think this answer is useful

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.

For example:

INSERT INTO MyTable
  ( Column1, Column2, Column3 )
VALUES
  ('John', 123, 'Lloyds Office'), 
  ('Jane', 124, 'Lloyds Office'), 
  ('Billy', 125, 'London Office'),
  ('Miranda', 126, 'Bristol Office');

The Answer 2

814 people think this answer is useful

If you are inserting into a single table, you can write your query like this (maybe only in MySQL):

INSERT INTO table1 (First, Last)
VALUES
    ('Fred', 'Smith'),
    ('John', 'Smith'),
    ('Michael', 'Smith'),
    ('Robert', 'Smith');

The Answer 3

136 people think this answer is useful

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.

The Answer 4

84 people think this answer is useful

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.

Example:

INSERT INTO tbl_name (a,b,c) VALUES(1,2,3),(4,5,6),(7,8,9);

Add a Comment