Home Inserting multiple rows in a single SQL query?
Reply: 4

Inserting multiple rows in a single SQL query?

Faisal
1#
Faisal Published in 2009-01-17 05:55:54Z

This question already has an answer here:

  • How do I insert multiple rows WITHOUT repeating the “INSERT INTO dbo.Blah” part of the statement? 12 answers

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?

Faisal
2#
Faisal Reply to 2017-02-01 08:26:01Z

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');
a_horse_with_no_name
3#
a_horse_with_no_name Reply to 2017-02-03 08:40:58Z

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.

Michel Gokan
4#
Michel Gokan Reply to 2016-03-13 15:06:33Z

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.

Sparkup
5#
Sparkup Reply to 2011-07-29 11:00:19Z

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);
You need to login account before you can post.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.313077 second(s) , Gzip On .

© 2016 Powered by mzan.com design MATCHINFO