Inserting multiple rows in a single SQL query?

Question

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?

**Moderator Note**: Please take all discussion about the merits of this question to [this meta post](http://meta.stackexchange.com/questions/194313/why-was-inserting-multiple-rows-in-a-single-sql-query-closed). — George Stocker, Aug 22 '13 at 13:39
To insert multiple record in one line you can try this also Example: insert into tablename (col1 ,col2) select uid,uname from usertable; — Satendra Jindal, Mar 04 '15 at 12:35
you can also use this query for inserting multiple rows in a single SQL query. here is the query : INSERT into tablename1(Person, Id, Office) SELECT 'John', 1,'Lloyds Office' UNION SELECT 'Jane', 2,'Lloyds Office' UNION SELECT 'Billy', 3,'Lloyds Office' UNION SELECT 'Miranda', 4,'Lloyds Office' — Laxmi, Oct 19 '16 at 06:02
As far as I can tell, the name for this technique is nothing specific, just `insert multiple rows` , for those wishing to be able to refer to the technique conceptually. Ref: https://dev.mysql.com/doc/refman/5.5/en/insert.html — Kzqai, Dec 28 '16 at 22:45
@Kzqai, what about `insert set col1='val1', col2='val2 (?) col1='val3', col2=val2'` in/for [tag:mysql] ? I mean inserting multiple rows with mysql-specific `insert set` syntax; is it possible? — , Sep 10 '17 at 11:27
@Chinggis6 Yes, trivially possible, just use a select for the column values: — Kzqai, Sep 11 '17 at 17:24
@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. — Kzqai, Sep 11 '17 at 21:21
@Kzqai No. Please recheck the link you provided above. MySQL, unlike other DBs, let you use `insert` in the `update` fashion with `set` keyword rather than `values`. It is very MySQL specific non-standard SQL statement. — , Sep 11 '17 at 21:26
@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. https://dev.mysql.com/doc/refman/5.5/en/insert.html — Kzqai, Sep 12 '17 at 13:43
INSERT INTO MyTable VALUES ("John", 123, "Lloyds Office"),("Jane", 124, "Lloyds Office"),("Billy", 125, "London Office"),("Miranda", 126, "Bristol Office"); — David Marabottini, Nov 15 '18 at 14:51
@GeorgeStocker, it's interesting that this question has been closed as a duplicate of a lower quality question (in terms of views, and upvotes) that was asked over a year **after** this question. Isn't the convention that the question asked first is the one that is kept open? — Doctor Jones, Oct 17 '19 at 13:28
Is there any performance difference between these two methods if we take 5000 rows? — zulqadar idrishi, Mar 25 '21 at 07:41

score 2628 · Answer 1 · edited Jan 01 '19 at 00:54

2628

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

edited Jan 01 '19 at 00:54

Soviut

88,194
49
192
260

answered Jan 17 '09 at 07:14

BinaryMisfit

29,219
2
37
44

430

And please note that the maximum number of rows in one insert statement is 1000. – cryss Mar 21 '13 at 13:39
218

That should be phrased "the maximum number of rows in one `VALUES` clause is 1000". It's not the `INSERT` statement that is limited to 1000 rows. – Anon Dec 19 '13 at 17:58
@Anon Thanks for clearing up ChrisJ's very misleading comment. Saved me from some painful debugging. :) – async Mar 11 '14 at 13:33
41

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. – Edwin Stoteler Apr 24 '14 at 12:07
8

This doesn't work with SQL Server 2005, see http://stackoverflow.com/questions/2624713/how-do-i-insert-multiple-rows-without-repeating-the-insert-into-dbo-blah-part – pkr Apr 28 '14 at 16:40
1

you don't have a semicolon at the end of the statement, whereas other answers do. do you need it or no? – ahnbizcad Dec 07 '15 at 18:51
5

@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 Dec 09 '15 at 03:51
1

@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](https://msdn.microsoft.com/en-us/library/ms177563.aspx). I am also adding a link to [an interesting article on the subject](http://www.dbdelta.com/always-use-semicolon-statement-terminators/). Thanks ! – salcoin Dec 12 '15 at 17:54
This is also slower than what he is currently doing: http://stackoverflow.com/questions/8635818/multiple-insert-statements-vs-single-insert-with-multiple-values – Zar Shardan Aug 08 '16 at 23:24
@Anon If you need more than 1000 rows you could union multiple 1000-row `VALUES` constructors in a CTE for your insert. Still counts as one query! – NReilingh Mar 09 '17 at 20:04
do you know in which sql specification was introduced? (multiple VALUES) – DanielV Jul 27 '17 at 08:50
Maximum number of rows is 1000, BUT the optimum number is anything between 10 and 100 (depending on columns count). Read [this article](https://www.red-gate.com/simple-talk/sql/performance/comparing-multiple-rows-insert-vs-single-row-insert-with-three-data-load-methods/) – ABS Nov 20 '17 at 17:40
Can anyone answer this? https://stackoverflow.com/questions/51763204/sql-inserting-multiple-records-updates-existing-in-single-query Basically same question but with update if existing – DayIsGreen Aug 09 '18 at 09:52
Another important difference: If you would try to insert records where the type of values aren't the same, it will not work using only one insert. When multiple inserts are used, the RDBMS is able to convert them to the target type one by one. But if the first record contain integer, the second a date, and the target type is string, automatic conversion is not applied. This sucks when the table is used as general parameter storage, and value could be anything. – klenium Mar 25 '21 at 18:43

score 880 · Answer 2 · edited Feb 01 '17 at 08:26

880

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

edited Feb 01 '17 at 08:26

Faisal

4,591
3
40
49

answered Jan 17 '09 at 06:10

too much php

88,666
34
128
138

60

As of SQL Server 2008, this will work if you replace the double quotes with single ones. – Valentino Vranken Mar 22 '12 at 13:37
21

Also works with postgres v9.0 – suspectus Nov 13 '13 at 10:19
13

And with SQLite – Michał Klimczak Dec 09 '13 at 10:13
7

Only SQLite 3.7.11 onwards. If you cannot guarantee that, use the UNION method shown here: http://stackoverflow.com/a/5009740/841830 – Darren Cook Feb 13 '14 at 01:12
1

is there any limit on values? like if i have many records then how to deal with this situaton? – Muneem Habib Sep 28 '15 at 07:32
Doesn't work in MS SQL DataWarehouse – Michael Freidgeim Oct 25 '16 at 03:58
It worked in SQL Server 2012 just fine. – John Pittaway Feb 22 '18 at 17:52
5

Muneem, the limit is 1,000 VALUE lines per INSERT statement. – John Pittaway Feb 22 '18 at 18:02
@too It's also working Well on SQL SERVER 2014 – PatsonLeaner Jun 11 '18 at 12:04
What about this question? https://stackoverflow.com/questions/51763204/sql-inserting-multiple-records-updates-existing-in-single-query Basically same concern but has the function of updating in case of duplicate record – DayIsGreen Aug 09 '18 at 09:56

score 155 · Answer 3 · edited Feb 03 '17 at 08:40

155

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.

edited Feb 03 '17 at 08:40

answered Jan 17 '09 at 06:16

DavGarcia

18,540
14
58
96

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. – Michael Larsson Aug 19 '20 at 16:20
Starting from Oracle version 23c, Oracle now supports Value constructors as well. – Vijay Balebail Aug 16 '23 at 13:59

score 91 · Answer 4 · edited Jul 29 '11 at 11:00

91

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

edited Jul 29 '11 at 11:00

Sparkup

3,686
2
36
50

answered Apr 01 '09 at 14:07

3

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 Sep 07 '16 at 16:41
13

@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. – tomosius Jan 21 '17 at 06:36
1

you can create one CTE and use insert into YourTable (ID,Name) From select ID,Name From CTE – Mohammad Farahani Jul 25 '17 at 05:15
What about this question? https://stackoverflow.com/questions/51763204/sql-inserting-multiple-records-updates-existing-in-single-query Basically same concern but has the function of updating in case of duplicate record – DayIsGreen Aug 09 '18 at 09:56
3

@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. – uncoder Sep 13 '18 at 18:46

Inserting multiple rows in a single SQL query?

4 Answers4

Linked

Related