CODEWITHSUNDEEP
sql-servert-sqlstored-procedurestransactions
user1400915
user1400915

Reputation: 1943

Using Transactions - Best practices

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?

Upvotes: 4

Views: 7424

Answers (3)

paparazzo
paparazzo

Reputation: 45096

As stated a single statement is a transaction.

Sort the insert on the primary key of TABLEB to slow down fragmentation.

One thing to be careful of is inserting the same data multiple times.
If you want to prevent that then left join on a pk. 

declare @Ta table (id int identity primary key, name varchar(10));
declare @Tb table (id int          primary key, name varchar(10));
insert into @Ta values ('name'), ('name'), ('name'), ('name'), ('name'), ('name'), ('name'), ('nameL');

insert into @Tb (id, name)
select id, name from @Ta order by id;

select * from @Tb;

insert into @Tb (id, name)
select ta.id, ta.name 
from @Ta ta 
left join @Tb tb 
  on tb.id = ta.id 
where tb.id is null 
order by ta.id;

select * from @Tb;

If you want to update data that is present search on the merge command.

Upvotes: 2

gotqn
gotqn

Reputation: 43626

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

Upvotes: 11

MK_
MK_

Reputation: 1169

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:

Upvotes: 2

Related Questions