By rits


2009-01-17 05:55:54 8 Comments

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?

4 comments

@BinaryMisfit 2009-01-17 07:14:14

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

@cryss 2013-03-21 13:39:29

And please note that the maximum number of rows in one insert statement is 1000.

@Anon 2013-12-19 17:58:48

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.

@async 2014-03-11 13:33:14

@Anon Thanks for clearing up ChrisJ's very misleading comment. Saved me from some painful debugging. :)

@Edwin Stoteler 2014-04-24 12:07:40

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.

@pkr298 2014-04-28 16:40:54

This doesn't work with SQL Server 2005, see stackoverflow.com/questions/2624713/…

@ahnbizcad 2015-12-07 18:51:25

you don't have a semicolon at the end of the statement, whereas other answers do. do you need it or no?

@NReilingh 2015-12-09 03:51:21

@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.

@salcoin 2015-12-12 17:54:24

@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 !

@Zar Shardan 2016-08-08 23:24:49

This is also slower than what he is currently doing: stackoverflow.com/questions/8635818/…

@NReilingh 2017-03-09 20:04:08

@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!

@DanielV 2017-07-27 08:50:41

do you know in which sql specification was introduced? (multiple VALUES)

@ABS 2017-11-20 17:40:18

Maximum number of rows is 1000, BUT the optimum number is anything between 10 and 100 (depending on columns count). Read this article

@DayIsGreen 2018-08-09 09:52:26

Can anyone answer this? stackoverflow.com/questions/51763204/… Basically same question but with update if existing

@DavGarcia 2009-01-17 06:16:23

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.

@too much php 2009-01-17 06:10:26

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

@Valentino Vranken 2012-03-22 13:37:41

As of SQL Server 2008, this will work if you replace the double quotes with single ones.

@suspectus 2013-11-13 10:19:24

Also works with postgres v9.0

@MichaƂ K 2013-12-09 10:13:07

And with SQLite

@Darren Cook 2014-02-13 01:12:58

Only SQLite 3.7.11 onwards. If you cannot guarantee that, use the UNION method shown here: stackoverflow.com/a/5009740/841830

@Muneem Habib 2015-09-28 07:32:37

is there any limit on values? like if i have many records then how to deal with this situaton?

@Michael Freidgeim 2016-10-25 03:58:44

Doesn't work in MS SQL DataWarehouse

@John Pittaway 2018-02-22 17:52:21

It worked in SQL Server 2012 just fine.

@John Pittaway 2018-02-22 18:02:45

Muneem, the limit is 1,000 VALUE lines per INSERT statement.

@PatsonLeaner 2018-06-11 12:04:37

@too It's also working Well on SQL SERVER 2014

@DayIsGreen 2018-08-09 09:56:05

What about this question? stackoverflow.com/questions/51763204/… Basically same concern but has the function of updating in case of duplicate record

@ 2009-04-01 14:07:24

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

@M T Head 2016-09-07 16:41:10

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.

@tomosius 2017-01-21 06:36:38

@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.

@Mohammad Farahani 2017-07-25 05:15:50

you can create one CTE and use insert into YourTable (ID,Name) From select ID,Name From CTE

@DayIsGreen 2018-08-09 09:56:13

What about this question? stackoverflow.com/questions/51763204/… Basically same concern but has the function of updating in case of duplicate record

@uncoder 2018-09-13 18:46:48

@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.

Related Questions

Sponsored Content

13 Answered Questions

[SOLVED] Best way to get identity of inserted row?

  • 2008-09-03 21:32:02
  • Oded
  • 784104 View
  • 1039 Score
  • 13 Answer
  • Tags:   sql sql-server tsql

33 Answered Questions

[SOLVED] How do I UPDATE from a SELECT in SQL Server?

28 Answered Questions

47 Answered Questions

29 Answered Questions

37 Answered Questions

40 Answered Questions

[SOLVED] How to return only the Date from a SQL Server DateTime datatype

16 Answered Questions

[SOLVED] How to insert an item into an array at a specific index (JavaScript)?

28 Answered Questions

[SOLVED] How can I prevent SQL injection in PHP?

25 Answered Questions

[SOLVED] How do I perform an IF...THEN in an SQL SELECT?

Sponsored Content