Backing up two tables using SQL transactions

Question

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

Answer 1

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