T-SQL:在 OUTPUT 子句中插入原始值

本文介绍了T-SQL:在 OUTPUT 子句中插入原始值的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

我有一个表 Items (ItemID, Name, ...) 其中 ItemID 是自动生成的身份

I have a table Items (ItemID, Name, ...) where ItemID is auto-generated identity

我想在同一张表上的 FROM 选择中将行添加到该表中.并将 OriginalItemID 和 NewlyGeneratedID 之间的引用保存到表变量中.

I want to add rows into this table FROM select on this same table.AND save into table variable the references between OriginalItemID and NewlyGeneratedID.

所以我希望它看起来像下面这样:

So I want it to look like the following:

DECLARE @ID2ID TABLE (OldItemID INT, NewItemID INT);

INSERT INTO Items OUTPUT Items.ItemID, INSERTED.ItemID INTO @ID2ID
SELECT * FROM Items WHERE Name = 'Cat';

BUT Items.ItemID 显然在这里不起作用.是否有一种解决方法可以使 OUTPUT 从 SELECT 语句中获取原始 ItemID?

BUT Items.ItemID obviously does not work here. Is there a workaround to make OUTPUT take original ItemID from the SELECT statement?

推荐答案

如果您使用的是 SQL Server 2008+，则可以使用 MERGE 用于获取当前 ID 和新 ID.this question 中描述了该技术.

If you are on SQL Server 2008+, you can use MERGE for getting both the current and the new ID. The technique is described in this question.

对于您的示例，语句可能如下所示:

For your example the statement might look like this:

MERGE INTO
  Items AS t
USING
  (
    SELECT *
    FROM Items
    WHERE Name = 'Cat'
  ) AS s
ON
  0 = 1
WHEN NOT MATCHED BY TARGET THEN
  INSERT (target_column_list) VALUES (source_column_list)
OUTPUT
  S.ItemID, INSERTED.ItemID INTO @ID2ID (OldItemID, NewItemID)
;

这篇关于T-SQL:在 OUTPUT 子句中插入原始值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！