INSERT SELECT Statement and Rollback SQL

Question

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

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

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?

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

Answer 1

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

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

Answer 2

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?

Answer 3

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:

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.