Using Transactions - Best practices

Question

Assuming that I have a procedure in SQL Server as:

create procedure BULKINSERT
AS
 INSERT INTO TABLEB (
 SELECT NAME, ID From TableA
)
GO   

A simple one which reads data from TABLE A and insert into TABLE B. If I have 1 million records to be inserted into table B and if one record fails for whatever reason , in this scenario should I use TRANSACTION or not ?

Should I rollback the entire operation?

Answer 1

You can use the following template for your stored procedures:

SET NOCOUNT, XACT_ABORT ON;

    BEGIN TRY

        BEGIN TRANSACTION;
        -- CODE BLOCK GOES HERE
        COMMIT TRANSACTION;

    END TRY
    BEGIN CATCH 

       IF @@TRANCOUNT > 0
       BEGIN
          ROLLBACK TRANSACTION
       END;

       -- GET ERRORS DETAILS OR THROW ERROR

    END CATCH;

SET NOCOUNT, XACT_ABORT OFF;

More details:

• XACT_ABORT - specifies whether SQL Server automatically rolls back the current transaction when a Transact-SQL statement raises a run-time error;

• if you need information about the error (ERROR_MESSAGE, ERROR_LINE, ERROR_NUMBER, ERROR_PROCEDURE, ERROR_SEVERITY, ERROR_STATE)

This is a general technique for working with transactions. I would recommend the following articles of Erland Sommarskog:

1. Error and Transaction Handling in SQL Server Part One – Jumpstart Error Handling
2. Error and Transaction Handling in SQL Server Part Two – Commands and Mechanisms

Answer 2

Since one statement is always considered atomic, and you have only 1 statement here, either 1 million records is inserted into TABLEB or none are. However, you might want to handle the error if it occurs in which case you should put your INSERT into a TRY block and add a CATCH one to handle what to do in case of error.

To read up more on the subject, check the following sources for good starting points:

• https://learn.microsoft.com/en-us/sql/t-sql/language-elements/transactions-transact-sql
• http://www.sqlteam.com/article/introduction-to-transactions
• https://www.mssqltips.com/sqlservertutorial/3305/what-does-begin-tran-rollback-tran-and-commit-tran-mean/