Post reply

Transactions in an archive process

  • February 4, 2011 at 2:16 pm

    #233180

    Hello, please help as I am new to using transactions. I want to move data from a source to an archive table. There are about 700,00 fairly small rows.

    DECLARE @TransactionName varchar(20) = 'Archive';

    BEGIN TRANSACTION @TransactionName

    INSERT INTO Archive

    ....

    SELECT

    ...

    FROM Source

    WHERE DateTimeStamp < '20090701'

    DELETE FROM Source

    WHERE DateTimeStamp < '20090701'

    COMMIT TRANSACTION @TransactionName

    Is this all I need to do to be sure both statements run or fail together?

  • February 7, 2011 at 7:28 am

    #1283107

    No. You need to look into error checking and rollbacks. Without that, your transaction code is incomplete.

    Technically speaking (ACID test talk), if the statements fail due to syntax or server errors, they should roll back automatically. But always, always, to be safe, you should include error handling in your transactions so that it does what you want it to do upon an error.

    Using Try...Catch within a transaction is a good idea to take care of situations like this.

    Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

  • February 7, 2011 at 8:35 am

    #1283155

    Brandie has a great point. What you have written will move everything, but you ought to have error handling.

    I might also write a "check" routine that verifies the counts of things moved before the delete, or maybe even after. Store the count of rows moving in another table so you can track what gets archived, and also how long it takes. Those metrics might help you over time in tuning this.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply