Ensure Successful INSERT before triggering DELETE

Question

Am currently working on archiving a database, I have come up with a simple approach. However, executing the script can run into errors. Leading to a case where the insert is not successfully executed but the delete. Which means I delete records from production before inserting them into archive. Is there a way to ensure delete statement would not be executed unless insert is run successfully?

INSERT INTO [archive].[dbo].[Table]
SELECT *
FROM [Production].[dbo].[Table]
WHERE TimeStamp < DATEADD(year,-2,SYSDATETIME()) 

DELETE FROM [Production].[dbo].[table]
WHERE TimeStamp < DATEADD(year,-2,SYSDATETIME())

you can put a commit statement after the insert statement and then use delete. — dassum, Jul 19 '20 at 09:05
[This](https://stackoverflow.com/questions/10153648/correct-use-of-transactions-in-sql-server) might help — Singh, Jul 19 '20 at 10:43

score 2 · Answer 1 · answered Jul 19 '20 at 10:39

As an alternative to an explict transaction, one can specify an OUTPUT clause on the DELETE to perform the operation as a single autocommit transaction. This will ensure all-or-none behavior.

DELETE [Production].[dbo].[Table]
OUTPUT DELETED.*   
INTO [archive].[dbo].[Table]
WHERE TimeStamp < DATEADD(year,-2,SYSDATETIME());

Also, consider an explict column list instead of *.

Sparky · Answer 2 · 2020-07-19T09:35:52.783

Typically, you would create a transaction, making the operation into a single update.

BEGIN TRAN

BEGIN TRY
    INSERT INTO [archive].[dbo].[Table] 
    SELECT * FROM [Production].[dbo].[Table] WHERE TimeStamp < 
             DATEADD(year,-2,SYSDATETIME())

    DELETE FROM [Production].[dbo].[table] 
    WHERE TimeStamp < DATEADD(year,-2,SYSDATETIME())

    COMMIT
END TRY
BEGIN CATCH
   ROLLBACK
END CATCH

Basically, the code says.

BEGIN TRAN - Start a group of commands that either all complete or none complete
BEGIN TRY - Commands to try
COMMIT - If I reach here, everything worked OK
BEGIN CATCH
ROLLBACK If an error occurs, roll back the commands (basically ignore them)

You should probably add some status to indicate success or failure, and maybe capture the error in the BEGIN CATCH block, but this should give you enough to get started

A second approach is to modify your DELETE statement a bit

DELETE FROM [Production].[dbo].[table] 
    WHERE TimeStamp IN (SELECT TimeStamp FROM [archive].[dbo].[Table])

Good luck

I suggest catch block `IF @@TRANCOUNT > 0 ROLLBACK;THROW;` to raise an error instead of silently ignoring it. It's also a good idea to specify `SET XACT_ABORT ON;` to ensure the explict transaction is rolled back immediately should a client query timeout occur since the catch block code will not execute in that case. — Dan Guzman, Jul 19 '20 at 10:32
Fully agree, the error from the BEGIN CATCH should not be ignored. How it is captured is something the developer needs to consider... Thanks for the comments — Sparky, Jul 19 '20 at 10:35

Ensure Successful INSERT before triggering DELETE

2 Answers2