创建列并插入同一事务中？ [英] Create column and insert into it within the same transaction?

问题描述

是否可以在同一笔交易中创建一列并将值插入其中？这是升级脚本的一部分。我发现以下方法在线，但是它不起作用；我收到一个错误：无效的列名 IndexNumber。。我认为这是因为事务尚未创建该列，所以没有要插入的内容。

Is it possible to create a column and insert values to it during the same transaction? This is part of an upgrade script. I found the following method online, but it does not work; I get an error: Invalid column name 'IndexNumber'.. I'm assuming this is because the transaction hasn't created the column yet so there is nothing to insert to.

脚本的相关部分：

Print 'Beginning Upgrade'
Begin Transaction
    -- --------------------------------------------------------------------
    USE [MyDatabase];

    /* Widgets now can be ordered and the order can be modified */
    ALTER TABLE [dbo].[Widgets] ADD [IndexNumber] [int] NULL;

    DECLARE @ind INT 
    SET @ind = 0 
    UPDATE [dbo].[Widgets]
    SET @ind = [IndexNumber] = @ind + 1;

    ALTER TABLE [dbo].[Widgets] ALTER COLUMN [IndexNumber] [int] NOT NULL;
    -- --------------------------------------------------------------------
Commit tran
Print 'Upgrade completed'

[IndexNumber] 不是身份列的原因是它必须可编辑。 / p>

The reason why [IndexNumber] is not an identity column is that it must be editable.

推荐答案

如果您不想将代码分为多个批次，则可以使用 EXEC 来创建嵌套作用域/批处理：

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

Print 'Beginning Upgrade'
Begin Transaction
    -- --------------------------------------------------------------------
    USE [MyDatabase];

    /* Widgets now can be ordered and the order can be modified */
    ALTER TABLE [dbo].[Widgets] ADD [IndexNumber] [int] NULL;

    EXEC('DECLARE @ind INT 
    SET @ind = 0 
    UPDATE [dbo].[Widgets]
    SET @ind = [IndexNumber] = @ind + 1;');

    ALTER TABLE [dbo].[Widgets] ALTER COLUMN [IndexNumber] [int] NOT NULL;
    -- --------------------------------------------------------------------
Commit tran
Print 'Upgrade completed'

原始代码无法运行的原因是，它尝试在运行整个批次之前编译-编译失败因此它甚至从不开始事务，更不用说更改表了。

The reason why the original code doesn't work because it tries to compile the entire batch before running it - the compilation fails and so it never even starts the transaction, let along alters the table.

这篇关于创建列并插入同一事务中？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！