SQL Server 单个插入语句中可插入的最大行数
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/37471803/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL Server Maximum rows that can be inserted in a single insert statment
提问by Shachaf.Gortler
I want to do a batch insert, similar to this question
我想做一个批量插入，类似于这个问题
How to do a batch insert in MySQL
What is the limitation is SQL Server on how many rows can be inserted in a single insert statement ?
What happens when for example the first value is inserted but the second one causes a primary key violation. Are the all
INSERTstatements rolled back?
SQL Server 在单个插入语句中可以插入多少行的限制是什么？
例如，当插入第一个值但第二个值导致主键冲突时会发生什么。所有
INSERT语句都回滚了吗？
INSERT INTO tbl_name (a,b)
VALUES (1, 2), (1, 3));
回答by M.Ali
The Maximum number of rows you can insert in one statement is 1000 when using INSERT INTO ... VALUES...i.e.
使用INSERT INTO ... VALUES...ie时，您可以在一个语句中插入的最大行数为 1000
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...
但是，如果您使用 SELECT 语句在表中插入行，则没有限制，例如...
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.
在上述情况下，每行插入都是一个单独的语句，如果任何行插入导致错误，只有特定的插入语句将被回滚，其余的将被成功插入。
回答by Dutchman
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
回答by Todd Menier
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:
尽管最大值为 1000，但已经证明性能在更小的数字时开始下降。Eugene Philipov 写了一篇很好的文章来探讨这个话题：
To summarize, the author did some very well-designed experimenting and found a sweet spot at around 25. YMMV.
总而言之，作者做了一些非常精心设计的实验，并在25左右找到了一个甜蜜点。天啊。
