插入SELECT语句和回滚SQL

Im Working on a creation of a query who uses INSERT SELECT statement using MS SQL Server 2008:

Im正在创建使用MS SQL Server 2008的INSERT SELECT语句的查询:

INSERT INTO TABLE1 (col1, col2) SELECT col1, col2 FROM TABLE2

插入表1 (col1, col2)从表2中选择col1, col2

Right now the excecution of this query is inside a transaction:

现在，这个查询的问题是在一个事务中:

Pseudocode:

伪代码:

try  
{  
    begin transaction;  
    query;  
    commit;  
}  
catch
{
rollback; 
}

If TABLE2 has around 40m of rows, at the moment of making the insert on the TABLE1, if there is an error in the middle of the INSERT, will the INSERT SELECT statement make a rollback itself or I need to use a transaction to preserve data integrity?

如果表2有大约40m行，那么在执行表1上的insert操作时，如果在insert中间有错误，那么insert SELECT语句本身会进行回滚，还是我需要使用事务来保持数据完整性?

It is necessary to use a transaction? or SQL SERVER it self uses a transaction for this type of sentences.

有必要使用事务吗?或者它自己为这种类型的句子使用事务。

3 个解决方案

#1

An individual SQL command is atomic. It will either complete or be rolled back.

单个SQL命令是原子的。它要么完成，要么回滚。

If completed, a transaction that wraps that command could roll it back. If it is outside a transaction, then it is commited on completion.

如果完成，包装该命令的事务可以回滚该命令。如果它在事务之外，则在完成时进行推荐。

#2

Statements are atomic - if the statement doesn't complete nothing will be changed. Also the documentation has some more specific information regarding failures during an insert statement:

语句是原子性的——如果语句不完整，则不会发生任何更改。此外，该文件还提供了关于insert语句中失败的一些更具体的信息:

If INSERT is loading multiple rows with SELECT, any violation of a rule or constraint that occurs from the values being loaded causes the complete statement to be stopped, and no rows are loaded.

如果INSERT正在用SELECT加载多行，那么从所加载的值中发生的任何违反规则或约束的行为都会导致完整语句被停止，并且不会加载任何行。

#3

SQL Server does implicit transactions in this case. The insert statement is atomic, either the whole statement will succeed or it will be rolled back if it fails. Remember ACID?

SQL Server在这种情况下执行隐式事务。insert语句是原子的，要么整个语句成功，要么失败就回滚。还记得酸吗?

#1