SQL Server 单个插入语句中可插入的最大行数 [英] SQL Server Maximum rows that can be inserted in a single insert statment

问题描述

我想做一个批量插入，类似于这个问题

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

如何在 MySQL 中进行批量插入

1. SQL Server 在单个插入语句中可以插入多少行的限制是什么?

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

例如，当插入第一个值但第二个值导致主键冲突时会发生什么.所有 INSERT 语句都回滚了吗?

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

推荐答案

当使用 INSERT INTO ... VALUES... 即

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

但是如果您使用 SELECT 语句在表中插入行，则没有限制，例如...

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.

这篇关于SQL Server 单个插入语句中可插入的最大行数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！