T-SQL 输出子句:如何访问旧的身份 ID [英] T-SQL Output Clause: How to access the old Identity ID

问题描述

我有一个 T-SQL 语句，它基本上执行插入操作，并将一些插入的值输出到表变量以供以后处理.

I have a T-SQL statement that basically does an insert and OUTPUTs some of the inserted values to a table variable for later processing.

有没有办法让我将所选记录的旧身份 ID 存储到我的表变量中.如果我使用下面的代码，我会得到无法绑定多部分标识符a.ID".错误.

Is there a way for me to store the old Identity ID of the selected records into my table variable. If I use the code below, I get "The multi-part identifier "a.ID" could not be bound." error.

DECLARE @act_map_matrix table(new_act_id INT, old_ID int)
DECLARE @new_script_id int
SET @new_script_id = 1

INSERT INTO Act
(ScriptID, Number, SubNumber, SortOrder, Title, IsDeleted)
OUTPUT inserted.ID, a.ID INTO @act_map_matrix
    SELECT 
        @new_scriptID, a.Number, a.SubNumber, a.SortOrder, a.Title, a.IsDeleted
    FROM Act a WHERE a.ScriptID = 2

谢谢！

推荐答案

您必须将 @act_map_matrix 重新加入 Act 以获得旧"值.

You'll have to join back @act_map_matrix onto Act to get the "old" value.

它在 INSERT 语句中根本不可用

It's simply not available in the INSERT statement

希望@new_scriptID 和"scriptid=2" 可以作为连接列

edit: one hopes that @new_scriptID and "scriptid=2" could be the join column

这篇关于T-SQL 输出子句:如何访问旧的身份 ID的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！