2097

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?

Doctor Jones
21.8k13 gold badges81 silver badges103 bronze badges
asked Jan 17, 2009 at 5:55
0

4 Answers 4

2799

In SQL Server 2008 you can insert multiple rows using a single 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');

This syntax is, however, limited to a maximum of 1,000 rows. If you need to INSERT more than 1,000 rows, this can be worked around using a derived table in a SELECT instead, which doesn't have the same limitation:

INSERT INTO MyTable ( Column1, Column2, Column3 )
SELECT V.Column1,
 V.Column2,
 V.Column3
FROM (VALUES('John', 123, 'Lloyds Office'), 
 ('Jane', 124, 'Lloyds Office'), 
 ('Billy', 125, 'London Office'),
 ...
 ('Sally', 10026, 'Bristol Office'))V(Column1, Column2, Column3);
Thom A ♦
97.8k12 gold badges67 silver badges102 bronze badges
answered Jan 17, 2009 at 7:14
Sign up to request clarification or add additional context in comments.

10 Comments

I know this question and answer are old, but I like to mention that there is a functional difference between using the method mentioned in the question and in this answer. The first executes triggers X amount of times with 1 record in the inserted table. The second executes triggers 1 time with x amount of records in the inserted table. Making the second method the best choice for performance assuming you made your triggers work correctly with batch inserts.
This doesn't work with SQL Server 2005, see stackoverflow.com/questions/2624713/…
@ahnbizcad Semicolons in T-sql are USUALLY optional, but they are reported to be required in the future. You should get yourself in the habit of using them to terminate each statement--your code will look nicer too IMO.
@NReilingh I was absolutely not aware of that. I actually proceeded to look it up. For reference, here is a link to the t-sql syntax conventions in the microsoft library. I am also adding a link to an interesting article on the subject. Thanks !
This is also slower than what he is currently doing: stackoverflow.com/questions/8635818/…
|
903

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');
Faisal
4,7854 gold badges43 silver badges51 bronze badges
answered Jan 17, 2009 at 6:10

10 Comments

As of SQL Server 2008, this will work if you replace the double quotes with single ones.
Also works with postgres v9.0
And with SQLite
Only SQLite 3.7.11 onwards. If you cannot guarantee that, use the UNION method shown here: stackoverflow.com/a/5009740/841830
Muneem, the limit is 1,000 VALUE lines per INSERT statement.
|
161

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.

answered Jan 17, 2009 at 6:16

2 Comments

Worked at first try, thanks! I could even add extra stuff like: 'Before date: ' + date2str(GETDATE()) + ' after date.' I know it is a bit of a strange comand date2str but is special syntax in my database.
Starting from Oracle version 23c, Oracle now supports Value constructors as well.
95

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);
Sparkup
3,7522 gold badges38 silver badges50 bronze badges
answered Apr 1, 2009 at 14:07

5 Comments

A better question is why would you want to? Why not just run the cleaner 4 commands all together in one batch. If one row fails your batch fails. if you do them individually grouped together 3 of 4 succeed.
@m-t-head I have an example of why I want to, and will give you 2 reasons. I am inserting into a table which has a data integrity checking trigger. Reason 1 - inserting values separately would violate the integrity check thus rolling back the transaction and returning an error. I am using SQL Server which does not support deferred constraints, so even if instead of a trigger it was a regular constraint, it would still not work. Reason 2 - integrity check is an expensive procedure and I'd rather have it executed once instead of 1000's of times per transaction. I'm still looking for solutions.
you can create one CTE and use insert into YourTable (ID,Name) From select ID,Name From CTE
What about this question? stackoverflow.com/questions/51763204/… Basically same concern but has the function of updating in case of duplicate record
@m-t-e-head The main reason is performance, actually. Inserting thousands or millions of rows would be much faster when you bundle them together into reasonably sized batches and using a single INSERT per batch.