25

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?

Piers Karsenbarg
  • 959
  • 2
  • 12
  • 23

4 Answers4

18
   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
Barak Gall
  • 103
  • 4
Pete Carter
  • 1,506
  • 11
  • 17
14

Yes, this is possible.

Most DDL statements can be rolled back in SQL Server (There are a few exceptions such as CREATE DATABASE)

Martin Smith
  • 87,941
  • 15
  • 255
  • 354
11

Yes, it is possible to use many ALTER TABLE statements in one transaction with ROLLBACK and COMMIT.

Here is a scaffold for your script (following MS guidelines with improvements):

BEGIN TRANSACTION

BEGIN TRY
    -- place your script in this TRY block

    -- your DDL instructions:
    ALTER TABLE1...
    ALTER TABLE2...
    -- data modifications:
    EXEC('
        UPDATE A
        SET    c1 = 23,
               c2 = ''ZZXX'';
    ');
    -- another DDL instruction:
    ALTER TABLE2...

    -- end of your script
END TRY
BEGIN CATCH
    IF @@TRANCOUNT > 0
        ROLLBACK TRANSACTION;


    -- If you want reraise an exception (to determine the reason of the exception)
    -- just uncomment block with appropriate version:

    -- SQL SERVER >= 2012
    /*
    THROW;
    */

    -- SQL SERVER < 2012 (tested against 2008 R2)
    /*
    DECLARE @ErrorMessage VARCHAR(MAX);
    DECLARE @ErrorSeverity INT;
    DECLARE @ErrorState INT;

    SELECT
        @ErrorMessage = ERROR_MESSAGE(),
        @ErrorSeverity = ERROR_SEVERITY(),
        @ErrorState = ERROR_STATE();

    RAISERROR (
        @ErrorMessage, -- Message text.
        @ErrorSeverity, -- Severity.
        @ErrorState -- State.
    );
    */
END CATCH;

IF @@TRANCOUNT > 0
    COMMIT TRANSACTION;
GO

Be careful, THROW works only for SQL SERVER version >= 2012. Here you could convert a version from semver to year notation: http://sqlserverbuilds.blogspot.ru (do not aware of .ru domain, there is an English version)

MarredCheese
  • 143
  • 5
mksm
  • 223
  • 2
  • 8
0

You need to look into transactions and exception handling in T-SQL. Check out the last two examples on this page: http://msdn.microsoft.com/en-us/library/ms175976.aspx

Michael
  • 113
  • 1
  • 5