INSERT SELECT Statement and Rollback SQL
- by Juan Perez
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.
