Azure SQL Server: 在前一个命令执行完之后才执行 SQL 命令

huangapple go评论129阅读模式
英文:

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

问题

我有一个在Microsoft Azure中运行的数据库。
我想在其中运行一个脚本。问题是,如果SQL命令一次运行多个,脚本只有在命令一个接一个地运行时才能正常工作。例如,SQL希望在列甚至创建之前就更新一个列。

例如,我希望确保在执行UPDATE之前首先执行SQL命令ADD。

我该如何确保脚本中的所有命令都是依次执行而不是并行执行的?

作为错误,我得到

执行查询失败。错误:无效的列名'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 -- 事务成功!
END TRY
BEGIN CATCH
    IF @@TRANCOUNT > 0
        ROLLBACK TRAN --出现错误时回滚事务

    -- <EDIT>:从SQL2008开始,必须按以下方式引发错误消息:
    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
英文:

I have a database running in Microsoft Azure.
I want to run a script in it. The problem is that the script only works if the SQL commands are run one at a time. So several commands are executed at the same time. For example, SQL wants to update a column before the column is even created.

E.g. I want to make sure that the SQL command ADD is executed first before UPDATE is executed.

How can I make it so that all commands in the script are executed one after the other and not in parallel?

As an error I get

Failed to execute query. Error: Invalid column name &#39;table_1_id&#39;

Here is my code:

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 &gt; 0
        ROLLBACK TRAN --RollBack in case of Error

    -- &lt;EDIT&gt;: 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);  
    -- &lt;/EDIT&gt;
END CATCH

答案1

得分: 2

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 -- 事务成功!
END TRY
BEGIN CATCH
    IF @@TRANCOUNT > 0
        ROLLBACK TRAN -- 在出现错误时回滚

    -- <EDIT>: 从SQL2008开始,必须使用以下方式引发错误消息:
    DECLARE @ErrorMessage NVARCHAR(4000);  
    DECLARE @ErrorSeverity INT;  
    DECLARE @ErrorState INT;  

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

    RAISEERROR (@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(N'UPDATE table_1 SET table_1_id = table_1_id2;')

  GO

  DROP TABLE table_1
英文:

Putting it in answer instead:

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

        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 &gt; 0
        ROLLBACK TRAN --RollBack in case of Error

    -- &lt;EDIT&gt;: 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);  
    -- &lt;/EDIT&gt;
END CATCH

This allows the new column to be visible to the update.
You can use the same technique for other parts if they're crashing

Testscript:

create table table_1 (table_1_id2 bigint)
GO

ALTER TABLE table_1
    ADD table_1_id BIGINT;
  exec(&#39;UPDATE table_1 SET table_1_id = table_1_id2;&#39;)

  go

  drop table table_1

答案2

得分: 2

以下是翻译好的部分:

在下面的代码中,我已经将更新语句更改为在动态 SQL 中运行,并且它正常运行。

代码

BEGIN TRY
BEGIN TRANSACTION
    ALTER TABLE table_1
    ADD table_1_id BIGINT;

    -- 更改为动态 SQL
    DECLARE @sql NVARCHAR(2048) = 'UPDATE table_1 SET table_1_id = table_1_id2;';
    EXEC sys.sp_executesql @query = @sql;

    ALTER TABLE table2
    DROP CONSTRAINT constraint1;

    ALTER TABLE table2
    DROP CONSTRAINT PK_1;

    ALTER TABLE table2
    DROP COLUMN table_1_id2;

    ALTER TABLE table2
    ADD table_1_id2 BIGINT IDENTITY PRIMARY KEY;
COMMIT TRANSACTION;
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0
    ROLLBACK TRANSACTION
DECLARE @ErrorMessage NVARCHAR(4000);
DECLARE @ErrorSeverity INT;
DECLARE @ErrorState INT;

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

RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState);
END CATCH

结果

Azure SQL Server: 在前一个命令执行完之后才执行 SQL 命令

英文:

Here in below code I have changed update statement to run in dynamic sql and it is working fine.

Code

BEGIN TRY
BEGIN TRANSACTION	
    ALTER TABLE table_1 
    ADD table_1_id BIGINT;
    
    --changed to dynamic sql
    DECLARE @sql NVARCHAR(2048) = &#39;UPDATE table_1 SET table_1_id = table_1_id2;&#39;;
    EXEC sys.sp_executesql @query = @sql;

    ALTER TABLE table2
    DROP CONSTRAINT constraint1;

    ALTER TABLE table2 
    DROP CONSTRAINT PK_1;

    ALTER TABLE table2 
    DROP COLUMN table_1_id2;

    ALTER TABLE table2 
    ADD table_1_id2 BIGINT IDENTITY PRIMARY KEY;    
COMMIT TRANSACTION;
END TRY
BEGIN CATCH
IF @@TRANCOUNT &gt; 0
    ROLLBACK TRANSACTION
DECLARE @ErrorMessage NVARCHAR(4000);  
DECLARE @ErrorSeverity INT;  
DECLARE @ErrorState INT;  

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

RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState);  
END CATCH

Result

Azure SQL Server: 在前一个命令执行完之后才执行 SQL 命令

huangapple
  • 本文由 发表于 2023年3月9日 18:28:47
  • 转载请务必保留本文链接:https://go.coder-hub.com/75683328.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定