SQL 将列添加到表中，然后在事务中更新它

Add column to table and then update it inside transaction

提问by Guillermo Gomez

I am creating a script that will be run in a MS SQL server. This script will run multiple statements and needs to be transactional, if one of the statement fails the overall execution is stopped and any changes are rolled back.

我正在创建一个将在 MS SQL 服务器中运行的脚本。此脚本将运行多个语句并且需要是事务性的，如果其中一个语句失败，则停止整体执行并回滚任何更改。

I am having trouble creating this transactional model when issuing ALTER TABLE statements to add columns to a table and then updating the newly added column. In order to access the newly added column right away, I use a GO command to execute the ALTER TABLE statement, and then call my UPDATE statement. The problem I am facing is that I cannot issue a GO command inside an IF statement. The IF statement is important within my transactional model. This is a sample code of the script I am trying to run. Also notice that issuing a GO command, will discard the @errorCode variable, and will need to be declared down in the code before being used (This is not in the code below).

在发出 ALTER TABLE 语句以将列添加到表然后更新新添加的列时，我无法创建此事务模型。为了立即访问新添加的列，我使用 GO 命令执行 ALTER TABLE 语句，然后调用我的 UPDATE 语句。我面临的问题是我无法在 IF 语句中发出 GO 命令。IF 语句在我的事务模型中很重要。这是我尝试运行的脚本的示例代码。还要注意，发出 GO 命令，将丢弃 @errorCode 变量，并且需要在使用之前在代码中声明（这不在下面的代码中）。

BEGIN TRANSACTION

DECLARE @errorCode INT
SET @errorCode = @@ERROR

-- **********************************
-- * Settings
-- **********************************
IF @errorCode = 0
BEGIN
 BEGIN TRY
  ALTER TABLE Color ADD [CodeID] [uniqueidentifier] NOT NULL DEFAULT ('{00000000-0000-0000-0000-000000000000}')
  GO
 END TRY
 BEGIN CATCH
  SET @errorCode = @@ERROR
 END CATCH
END

IF @errorCode = 0
BEGIN
 BEGIN TRY
  UPDATE Color
  SET CodeID= 'B6D266DC-B305-4153-A7AB-9109962255FC'
  WHERE [Name] = 'Red'
 END TRY
 BEGIN CATCH
  SET @errorCode = @@ERROR
 END CATCH
END

-- **********************************
-- * Check @errorCode to issue a COMMIT or a ROLLBACK
-- **********************************
IF @errorCode = 0
BEGIN
 COMMIT
 PRINT 'Success'
END
ELSE 
BEGIN
 ROLLBACK
 PRINT 'Failure'
END

So what I would like to know is how to go around this problem, issuing ALTER TABLE statements to add a column and then updating that column, all within a script executing as a transactional unit.

所以我想知道的是如何解决这个问题，发出 ALTER TABLE 语句来添加一列，然后更新该列，所有这些都在作为事务单元执行的脚本中进行。

采纳答案by Remus Rusanu

GO is not a T-SQL command. Is a batch delimiter. The client tool (SSM, sqlcmd, osql etc) uses it to effectively cutthe file at each GO and send to the server the individual batches. So obviously you cannot use GO inside IF, nor can you expect variables to span scope across batches.

GO 不是 T-SQL 命令。是批处理分隔符。客户端工具（SSM、sqlcmd、osql 等）使用它在每次 GO 时有效地剪切文件并将各个批次发送到服务器。所以很明显你不能在 IF 中使用 GO，你也不能期望变量跨越批次的范围。

Also, you cannot catch exceptions without checking for the XACT_STATE()to ensure the transaction is not doomed.

此外，如果不检查XACT_STATE()以确保事务不会失败，则无法捕获异常。

Using GUIDs for IDs is always at least suspicious.

使用 GUID 作为 ID 总是至少是可疑的。

Using NOT NULL constraints and providing a default 'guid' like '{00000000-0000-0000-0000-000000000000}'also cannot be correct.

使用 NOT NULL 约束并提供默认的 'guid' 之类的'{00000000-0000-0000-0000-000000000000}'也不正确。

Updated:

更新：

• Separate the ALTER and UPDATE into two batches.
• Use sqlcmd extensions to break the script on error. This is supported by SSMS when sqlcmd mode is on, sqlcmd, and is trivial to support it in client libraries too: dbutilsqlcmd.
• use XACT_ABORTto force error to interrupt the batch. This is frequently used in maintenance scripts (schema changes). Stored procedures and application logic scripts in general use TRY-CATCH blocks instead, but with proper care: Exception handling and nested transactions.

• 将 ALTER 和 UPDATE 分成两批。
• 使用 sqlcmd 扩展在出错时中断脚本。当 sqlcmd 模式打开时，这由SSMS支持，sqlcmd，并且在客户端库中支持它也很简单：dbutilsqlcmd。
• 用于XACT_ABORT强制错误中断批处理。这经常用于维护脚本（架构更改）。存储过程和应用程序逻辑脚本通常使用 TRY-CATCH 块，但要适当注意：异常处理和嵌套事务。

example script:

示例脚本：

:on error exit

set xact_abort on;
go

begin transaction;
go

if columnproperty(object_id('Code'), 'ColorId', 'AllowsNull') is null
begin
    alter table Code add ColorId uniqueidentifier null;
end
go

update Code 
  set ColorId = '...'
  where ...
go

commit;
go

Only a successful script will reach the COMMIT. Any error will abort the script and rollback.

只有成功的脚本才能到达COMMIT. 任何错误都会中止脚本并回滚。

I used COLUMNPROPERTYto check for column existance, you could use any method you like instead (eg. lookup sys.columns).

我曾经COLUMNPROPERTY检查列是否存在，您可以使用任何您喜欢的方法（例如 lookup sys.columns）。

回答by Mark Sowul

Orthogonal to Remus's comments, what you can do is execute the update in an sp_executesql.

与 Remus 的评论正交，您可以做的是在 sp_executesql 中执行更新。

ALTER TABLE [Table] ADD [Xyz] NVARCHAR(256);

DECLARE @sql NVARCHAR(2048) = 'UPDATE [Table] SET [Xyz] = ''abcd'';';
EXEC sys.sp_executesql @query = @sql;

We've needed to do this when creating upgrade scripts. Usually we just use GO but it has been necessary to do things conditionally.

我们在创建升级脚本时需要这样做。通常我们只使用 GO，但有条件地做事是必要的。

回答by gbn

I almost agree with Remus but you can do this with SET XACT_ABORT ON and XACT_STATE

我几乎同意 Remus 但你可以用 SET XACT_ABORT ON 和 XACT_STATE 做到这一点

Basically

基本上

• SET XACT_ABORT ON will abort each batch on error and ROLLBACK
• Each batch is separated by GO
• Execution jumps to the next batch on error
• Use XACT_STATE() will test if the transaction is still valid

• SET XACT_ABORT ON 将在错误和 ROLLBACK 时中止每个批次
• 每批由 GO 分隔
• 执行跳转到下一个批处理出错
• 使用 XACT_STATE() 将测试交易是否仍然有效

Tools like Red Gate SQL Compare use this technique

Red Gate SQL Compare 等工具使用这种技术

Something like:

就像是：

SET XACT_ABORT ON
GO
BEGIN TRANSACTION
GO

IF COLUMNPROPERTY(OBJECT_ID('Color'), 'CodeID', ColumnId) IS NULL
   ALTER TABLE Color ADD CodeID [uniqueidentifier] NULL
GO

IF XACT_STATE() = 1
  UPDATE Color
  SET CodeID= 'B6D266DC-B305-4153-A7AB-9109962255FC'
  WHERE [Name] = 'Red'
GO

IF XACT_STATE() = 1
 COMMIT TRAN
--else would be rolled back

I've also removed the default. No value = NULL for GUID values. It's meant to be unique: don't try and set every row to all zeros because it will end in tears...

我也删除了默认值。对于 GUID 值，无值 = NULL。它应该是独一无二的：不要尝试将每一行都设置为零，因为它会以眼泪结束......

回答by HLGEM

Have you tried it without the GO?

你试过没有 GO 吗？

Normally you should not mix table changes and data changes in the same script.

通常，您不应在同一个脚本中混合表更改和数据更改。

回答by M.Sabaa

Another alternative, if you don't want to split the code into separate batches, is to use EXEC to create a nested scope/batch as here

另一种选择，如果您不想将代码拆分为单独的批次，则使用 E​​XEC 来创建嵌套范围/批次， 如下所示

回答by davek

I think you can use a ";" to terminate and execute eachn individual command, rather than GO.

我认为您可以使用“;” 终止并执行每个单独的命令，而不是 GO。

Note that GO is not part of Transact-SQL:

请注意，GO 不是 Transact-SQL 的一部分：

http://msdn.microsoft.com/en-us/library/ms188037.aspx

http://msdn.microsoft.com/en-us/library/ms188037.aspx