current community

your communities

more stack exchange communities

Stack Exchange
tour help
Take the 2-minute tour ×
Code Review Stack Exchange is a question and answer site for peer programmer code reviews. It's 100% free, no registration required.
up vote 2 down vote favorite

I'm using a transaction statement in SQL Server 2012 for the first time.

I want to insert Table1's data in Table1_Backup before I delete everything from Table1. Same process for Table2. Should I name my transactions? Does this transaction code even look right?

DECLARE @err int
BEGIN TRANSACTION Table1
insert into [Table1_BU]
select * from [Table1] order by ID
SET @err = @@ERROR

IF @err = 0
BEGIN
delete from Table1
END

IF @err = 0
COMMIT TRANSACTION Table1
ELSE ROLLBACK TRANSACTION Table1

SET @err = 0
BEGIN TRANSACTION Table2
insert into [Table2_BU]
select * from Table2 order by ID
SET @err = @@ERROR

IF @err = 0
BEGIN
delete from Table2
END

IF @err = 0
COMMIT TRANSACTION Table2
ELSE ROLLBACK TRANSACTION Table2
share|improve this question

1 Answer 1

up vote 1 down vote accepted

First of all, you don't check @@ERROR after deleting which is not a good idea.

Second, you don't need to name transactions, but you can if you want to. Although, I'd recommend to avoid the same naming as you use for tables. For example, Table1_Backup is preferable in this case.

Let's make it more readable and compact. We have two ways.

1. XACT_ABORT

If you SET XACT_ABORT ON, transaction will automatically roll back on every run-time error.

SET XACT_ABORT ON

BEGIN TRANSACTION
    INSERT INTO [Table1_BU] SELECT * FROM [Table1] ORDER BY ID
    DELETE FROM Table1
COMMIT TRANSACTION

BEGIN TRANSACTION
    INSERT INTO [Table2_BU] SELECT * FROM [Table2] ORDER BY ID
    DELETE FROM Table2
COMMIT TRANSACTION

SET XACT_ABORT OFF

2. TRY ... CATCH

Second approach is to get it under control.

BEGIN TRY
    BEGIN TRANSACTION
        INSERT INTO [Table1_BU] SELECT * FROM [Table1] ORDER BY ID
        DELETE FROM Table1
    COMMIT TRANSACTION
END TRY
BEGIN CATCH
    ROLLBACK TRANSACTION
END CATCH

-- and, of course, the very same for the second table
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.