将OUTPUT insert.id与所选行中的值合并

Question

运行中SQL Server 2014。如何从一个表中插入多行，并将插入的数据与新ID结合在一起？

让我们来看一个简化的示例！

DECLARE @Old TABLE 
(
    [ID] [int] PRIMARY KEY,
    [Data] [int] NOT NULL
)

DECLARE @New TABLE
(
    [ID] [int] PRIMARY KEY,
    [OtherID] [int] NULL
)

INSERT INTO [dbo].[Test] ([Data])
OUTPUT inserted.[ID], [@Old].[ID] /* <--- not supported :( */ INTO @New
SELECT [Data]
FROM @Old

我需要将插入的ID与要插入的数据结合起来。我可以假定插入的行与所选行的顺序相同吗？（[Data]插入操作后，我将无法加入。）

更新

以下似乎是一个可能的解决方案，但我找不到证明它可行的证据。可以保证工作吗？

DECLARE @Old TABLE 
(
    [RowID] [int] PRIMARY KEY IDENTITY, -- Guaranteed insert order?     
    [ID] [int] NOT NULL,
    [Data] [int] NOT NULL
)

DECLARE @New TABLE
(
    [RowID] [int] PRIMARY KEY IDENTITY, -- Guaranteed insert order? 
    [ID] [int] NOT NULL,
    [OtherID] [int] NULL
)

INSERT INTO [dbo].[Test] ([Data])
OUTPUT inserted.[ID] INTO @New
SELECT [Data]
FROM @Old
ORDER BY [RowID]

这里的技巧是使用一个单独的identity列并ORDER BY用于选定的行，然后 在上加入 RowID。

Answer 1

您可以（MERGEwith
）使用withOUTPUT子句。

MERGE可以INSERT，UPDATE和DELETE行。就我们而言，我们只需要INSERT。1 = 0始终为假，因此NOT MATCHED BY TARGET始终执行该部分。通常，可能还有其他分支，请参阅文档。 WHEN MATCHED通常用于UPDATE; WHEN NOT MATCHED BY SOURCE通常用于DELETE，但是我们在这里不需要它们。

这种复杂的形式MERGE等效于simple
INSERT，但与simple不同，INSERT它的OUTPUT子句允许引用我们需要的列。它允许从源表和目标表中检索列，从而节省了旧ID和新ID之间的映射。

MERGE INTO [dbo].[Test]
USING
(
    SELECT [Data]
    FROM @Old AS O
) AS Src
ON 1 = 0
WHEN NOT MATCHED BY TARGET THEN
INSERT ([Data])
VALUES (Src.[Data])
OUTPUT Src.ID AS OldID, inserted.ID AS NewID
INTO @New(ID, [OtherID])
;

关于您的更新，并依赖于生成IDENTITY值的顺序。

在简单的情况下，当[dbo].[Test]hasIDENTITY列时，INSERTwithORDER BY 可以
保证生成的IDENTITY值将按指定的顺序排列。

INSERT INTO [dbo].[Test] ([Data])
SELECT [Data]
FROM @Old
ORDER BY [RowID]

但是，当您使用该OUTPUT子句时：

INSERT INTO [dbo].[Test] ([Data])
OUTPUT inserted.[ID] INTO @New
SELECT [Data]
FROM @Old
ORDER BY [RowID]

OUTPUT流中的行未排序。至少严格来说，ORDER BY查询中的查询适用于主INSERT操作，但是没有什么可以说明的顺序OUTPUT。因此，我不会尝试依靠它。使用MERGE或添加额外的列来显式存储ID之间的映射。