Search code examples
sqlsqltransaction

Safely move/archive Rows in SQL Server

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?

Solution

  • 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