Safely move/archive Rows in SQL Server

Question

I have this SQL-script/sp

    BEGIN TRAN
    BEGIN TRY
      INSERT INTO TblDest
      SELECT * FROM TblSource
      DELETE FROM TblSource
      COMMIT TRAN
    END TRY
    BEGIN CATCH
       ROLLBACK TRAN
    END CATCH

It moves all rows in TblSource to TblDest. I want to make sure that any rows that is inserted while this backup is done stays in TblSource.

If I set a breakpoint before the DELETE-line and opens another window and inserts a new Row in TestSource, then complete the script, the new rows is being deleted.

Do I need a Temp-table or can I lock the entire table?

score 2 · Accepted Answer · answered Jul 18 '18 at 12:10

You can use HOLDLOCK hint:

INSERT INTO TblDest
      SELECT * FROM TblSource WITH (HOLDLOCK)
      DELETE FROM TblSource

HOLDLOCK Is equivalent to SERIALIZABLE. HOLDLOCK applies only to the table or view for which it is specified and only for the duration of the transaction defined by the statement that it is used in. HOLDLOCK cannot be used in a SELECT statement that includes the FOR BROWSE option.

From MSDN: Table hints

score 1 · Answer 2 · edited Jul 18 '18 at 12:10

1

You can try like below query.

  BEGIN TRAN
        BEGIN TRY
          INSERT INTO TblDest SELECT * FROM TblSource
          DELETE FROM TblSource where [SomeId] in (select id from TblDest)
          COMMIT TRAN
        END TRY
        BEGIN CATCH
           ROLLBACK TRAN
        END CATCH

edited Jul 18 '18 at 12:10

dns_nx

3,651
4
37
66

answered Jul 18 '18 at 12:09

Deepak Kumar

648
6
14

Thanks! I dont know which solution is the best, perhaps voting or more comments will help me. I chosed Ricardos solution in the end to avoid one more select. Probably it doesnt matter in my case, but its only possible to select one as "correct" – Cowborg Jul 18 '18 at 12:19
...and the real "TblDest" is our archive table and is pretty massive now – Cowborg Jul 18 '18 at 12:25

Safely move/archive Rows in SQL Server

2 Answers2