35

I want to do a batch insert, similar to this question

How to do a batch insert in MySQL

  1. What is the limitation is SQL Server on how many rows can be inserted in a single insert statement ?

  2. What happens when for example the first value is inserted but the second one causes a primary key violation. Are the all INSERT statements rolled back?

INSERT INTO tbl_name (a,b) 
VALUES (1, 2), (1, 3));
Braiam
  • 1
  • 11
  • 47
  • 78
Shachaf.Gortler
  • 5,655
  • 14
  • 43
  • 71
  • 3
    If you have to ask then you should probably be using BULK INSERT. I would think the answer to your question could be found with a Google search. – Michael Z. May 26 '16 at 22:19
  • Yes I thought of that but , due to certian restrictions in the system BULK INSERT is not a valid option – Shachaf.Gortler May 26 '16 at 22:26
  • `INSERT VALUES` limit is 1000, but it could be overriden with **[INSERT INTO SELECT FROM VALUES](https://stackoverflow.com/a/42703601/5070879)**, as for second question in SQL world vast majority of statements are all-or-nothing. – Lukasz Szozda Jul 03 '20 at 19:27

7 Answers7

52

The Maximum number of rows you can insert in one statement is 1000 when using INSERT INTO ... VALUES... i.e.

INSERT INTO TableName( Colum1)
VALUES (1),
       (2),
       (3),...... upto 1000 rows.

But if your are using a SELECT statement to insert rows in a table, there is no limit for that, something like...

INSERT INTO TableName (ColName)
Select Col FROM AnotherTable

Now coming to your second question. What happens when an error occurs during an insert.

Well if you are inserting rows using multi-value construct 

INSERT INTO TableName( Colum1)
VALUES (1),
       (2),
       (3)

In the above scenario if any row insert causes an error the whole statement will be rolled back and none of the rows will be inserted.

But if you were inserting rows with a separate statement for each row i.e. ...

INSERT INTO TableName( Colum1) VALUES (1)
INSERT INTO TableName( Colum1) VALUES (2)
INSERT INTO TableName( Colum1) VALUES (3)

In the above case each row insert is a separate statement and if any row insert caused an error only that specific insert statement will be rolled back the rest will be successfully inserted.

M.Ali
  • 67,945
  • 13
  • 101
  • 127
  • 6
    It would be nice if you included in the answer a link to documentation where this limitation of 1000 rows per `INSERT .. VALUES` is written: [Table Value Constructor](https://msdn.microsoft.com/en-us/library/dd776382.aspx) – Vladimir Baranov May 27 '16 at 08:38
  • 1
    ...and the error message, which is: "The number of row value expressions in the INSERT statement exceeds the maximum allowed number of 1000 row values." – Nickolay Oct 10 '17 at 18:06
  • Look at my answer below to see how you can easily add more than 1000 rows. – Dutchman Oct 31 '19 at 04:13
  • Any way to surpass that 1000 limit and make it to 100000 ? – rinilnath Feb 20 '21 at 17:15
  • Is there also a limit for updates and deletes? Or does this limit only apply to insertion? – Ryker Apr 27 '22 at 19:34
  • 1
    @Ryker no there is no limit to updates or delete. – M.Ali Apr 28 '22 at 00:21
11

Although the max is 1000, it's been demonstrated that performance begins to diminish at much smaller numbers. Eugene Philipov wrote a great article exploring this very topic:

https://www.red-gate.com/simple-talk/sql/performance/comparing-multiple-rows-insert-vs-single-row-insert-with-three-data-load-methods/

To summarize, the author did some very well-designed experimenting and found a sweet spot at around 25. YMMV.

Todd Menier
  • 37,557
  • 17
  • 150
  • 173
11

You can actually pass in an unlimited number of records using a subquery.

;WITH NewData AS (SELECT * FROM ( VALUES  (1, 'A'),(2,'B'),(3,'C')) x (Id, SomeName))
INSERT INTO TableName (Column1, Column2) SELECT Id, SomeName FROM NewData
Dutchman
  • 800
  • 8
  • 11
  • I used that syntax, it also works: ```INSERT INTO [dbo].[Table] (Column Names...) SELECT ColumnNames... FROM ( VALUES (...), (...), (...), (...), ) x (ColumnNames....) ``` – mihkov Jan 12 '23 at 15:48
3

Dutchman's solution is cool, but it can be simplified. It turns out that you don't need the CTE. I tried it, and it works without the CTE, like this:

INSERT INTO TableName (Column1, Column2) SELECT Id, SomeName
  FROM  ( VALUES  (1, 'A'),(2,'B'),(3,'C')) x (Id, SomeName)
nospringchicken
  • 51
  • 2
  • As many already commented, this won't let you insert more than 1000 rows at a time.... – Dutchman Sep 22 '22 at 04:45
  • 2
    @Dutchman, It does work. I've used it in production. As the accepted answer states, inserting the result of a SELECT will work around the 1000-row limitation. That's why your CTE-based answer works--not because of the CTE, but because you're SELECTing out of it. It turns out that you don't need the CTE; you can select the data directly as I have above and it still works around the limitation. So insert using > 1000 rows won't work, but inserting from a select as I have above will work. This is as simple as I've gotten it down, unless someone can simplify it further. – nospringchicken Sep 23 '22 at 16:00
1

This is how I add more than 1000 values to the temp table.

CREATE TABLE #TempTable(ID int)

INSERT INTO #TempTable (ID)
SELECT * from  (VALUES (45764),(45763),(45762),(45761),(45760),(45759),(45758),(45757),(45756)....)AS temp (column1)
Arya Mohanan
  • 577
  • 5
  • 8
0

you can try this

with tempDataTable AS (SELECT *From (VALUES
(18001,79626,'1992-12-11','1993-12-11') -- this is data u want to insert
)x(empNO,sal,frmDate,toDate)) -- tempDataColoumns
INSERT INTO salaries(emp_no,salary,from_date,to_date) SELECT empNO,sal,frmDate,toDate from newData

Remove '--' at the time of query

David Buck
  • 3,752
  • 35
  • 31
  • 35
Anirudh Goel
  • 1
0

There's short workaround to avoid rows limit and still treat it like one statement (all goes in or if there's one error, everything is rolled back)

INSERT INTO tbl_name (a,b) 
SELECT 1,2 UNION ALL
SELECT 1,3 UNION ALL
SELECT 1,4 .......
mr R
  • 997
  • 2
  • 12
  • 25