sql - Azure SQL Server : Execute SQL command only after the previous one has been executed

Question

我有一个在 Microsoft Azure 中运行的数据库。我想在其中运行一个脚本。问题是该脚本仅在一次运行一个 SQL 命令时才有效。所以几个命令是同时执行的。例如，SQL 希望在创建列之前更新该列。

例如我想确保在执行 UPDATE 之前先执行 SQL 命令 ADD。

如何才能使脚本中的所有命令依次执行而不是并行执行？

作为一个错误，我得到了

Failed to execute query. Error: Invalid column name 'table_1_id'

这是我的代码:

BEGIN TRY
    BEGIN TRANSACTION
        ALTER TABLE table_1
        ADD table_1_id BIGINT;
        UPDATE table_1 SET table_1_id = table_1_id2;

        ALTER TABLE table2
        DROP CONSTRAINT constraint1;

        ALTER TABLE table_1 
        DROP CONSTRAINT PK_1;

        ALTER TABLE table_1 
        DROP COLUMN table_1_id2;

        ALTER TABLE table_1 
        ADD table_1_id2 BIGINT IDENTITY PRIMARY KEY;    
    COMMIT TRAN -- Transaction Success!
END TRY
BEGIN CATCH
    IF @@TRANCOUNT > 0
        ROLLBACK TRAN --RollBack in case of Error

    -- <EDIT>: From SQL2008 on, you must raise error messages as follows:
    DECLARE @ErrorMessage NVARCHAR(4000);  
    DECLARE @ErrorSeverity INT;  
    DECLARE @ErrorState INT;  

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

    RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState);  
    -- </EDIT>
END CATCH

Answer 1

将其放入答案中:

BEGIN TRY
    BEGIN TRANSACTION
        ALTER TABLE table_1
        ADD table_1_id BIGINT;
        EXEC(N'UPDATE table_1 SET table_1_id = table_1_id2;')

        ALTER TABLE table2
        DROP CONSTRAINT constraint1;

        ALTER TABLE table_1 
        DROP CONSTRAINT PK_1;

        ALTER TABLE table_1 
        DROP COLUMN table_1_id2;

        ALTER TABLE table_1 
        ADD table_1_id2 BIGINT IDENTITY PRIMARY KEY;    
    COMMIT TRAN -- Transaction Success!
END TRY
BEGIN CATCH
    IF @@TRANCOUNT > 0
        ROLLBACK TRAN --RollBack in case of Error

    -- <EDIT>: From SQL2008 on, you must raise error messages as follows:
    DECLARE @ErrorMessage NVARCHAR(4000);  
    DECLARE @ErrorSeverity INT;  
    DECLARE @ErrorState INT;  

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

    RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState);  
    -- </EDIT>
END CATCH

这使得新列对于更新可见。如果其他部分崩溃，您可以使用相同的技术

测试脚本:

create table table_1 (table_1_id2 bigint)
GO

ALTER TABLE table_1
    ADD table_1_id BIGINT;
  exec('UPDATE table_1 SET table_1_id = table_1_id2;')

  go

  drop table table_1