debugsqlModerate

Can I change table structure in a transaction and then roll it back if there is an error?

Submitted by: @import:stackexchange-dba·Mar 10, 2026·

Viewed 0 times

canerrorrollbacktransactionstructurethenandtherechange

Problem

I have some ALTER TABLE statements that I am running. Not all of them work (they are the result of running SQL Data Compare) and I want to group them in some transactions and roll back the statements if something goes wrong.

Is this possible, or is it only data that can be rolled back?

Solution

   BEGIN TRANSACTION
      BEGIN TRY
        ALTER TABLE1...
        ALTER TABLE2...
        -- Additional data/structural changes
        COMMIT
      END TRY
      BEGIN CATCH
         ROLLBACK;
         THROW; -- Only if you want reraise an exception (to determine the reason of the exception)
      END CATCH

Context

StackExchange Database Administrators Q#24883, answer score: 17

Revisions (0)

No revisions yet.