英文:
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 'table_1_id'
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 > 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
答案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'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
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('UPDATE table_1 SET table_1_id = table_1_id2;')
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
结果
英文:
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) = '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
Result
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论