将 OUTPUT insert.id 与选定行的值相结合 [英] Combine OUTPUT inserted.id with value from selected row
问题描述
运行 SQL Server 2014.如何从表中插入多行并将插入的数据与新 ID 合并?
Running SQL Server 2014. How can I insert multiple rows from a table and combine the inserted data with the new IDs?
让我们看一个精简的例子！
Let's look at a stripped-down example!
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] 上加入.)
I need to combine the inserted IDs with the data that is being inserted. Can I assume that the inserted rows are in the same order as the selected rows? (I will not be able to join on [Data] after the insert operation.)
以下似乎是一个可能的解决方案，但我找不到证明它有效的证据.能保证有效吗?
The following seems like a possible solution, but I cannot find proof that it works. Is it guaranteed to work?
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.
The trick here is to use a separate identity column and ORDER BY for the selected rows, and then joining on RowID.
推荐答案
您可以 (ab) 使用 MERGE 和 OUTPUT 子句.
You can (ab)use MERGE with OUTPUT clause.
MERGE 可以INSERT、UPDATE 和DELETE 行.在我们的例子中，我们只需要INSERT.1=0 总是假的，所以 NOT MATCHED BY TARGET 部分总是被执行.通常，可能还有其他分支，请参阅文档.WHEN MATCHED 通常用于UPDATE；WHEN NOT MATCHED BY SOURCE 通常用于DELETE，但我们这里不需要它们.
MERGE can INSERT, UPDATE and DELETE rows. In our case we need only to INSERT.
1=0 is always false, so the NOT MATCHED BY TARGET part is always executed.
In general, there could be other branches, see docs.
WHEN MATCHED is usually used to UPDATE;
WHEN NOT MATCHED BY SOURCE is usually used to DELETE, but we don't need them here.
这种复杂形式的 MERGE 等价于简单的 INSERT，但与简单的 INSERT 不同，它的 OUTPUT 子句允许引用我们需要的列.它允许从源表和目标表中检索列，从而节省旧 ID 和新 ID 之间的映射.
This convoluted form of MERGE is equivalent to simple INSERT,
but unlike simple INSERT its OUTPUT clause allows to refer to the columns that we need.
It allows to retrieve columns from both source and destination tables thus saving a mapping between old and new IDs.
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] 有 IDENTITY 列，然后 INSERT 和 ORDER BY 将保证生成的 IDENTITY 值将按指定的顺序排列.请参阅SQL Server 中的订购保证中的第 4 点.请注意，它不保证插入行的物理顺序，但它保证生成 IDENTITY 值的顺序.
In the simple case, when [dbo].[Test] has IDENTITY column, then INSERT with ORDER BY will guarantee that the generated IDENTITY values would be in the specified order. See point 4 in Ordering guarantees in SQL Server. Mind you, it doesn't guarantee the physical order of inserted rows, but it guarantees the order in which IDENTITY values are generated.
INSERT INTO [dbo].[Test] ([Data])
SELECT [Data]
FROM @Old
ORDER BY [RowID]
但是，当您使用 OUTPUT 子句时:
But, when you use the OUTPUT clause:
INSERT INTO [dbo].[Test] ([Data])
OUTPUT inserted.[ID] INTO @New
SELECT [Data]
FROM @Old
ORDER BY [RowID]
OUTPUT 流中的行没有排序.至少，严格来说，查询中的 ORDER BY 适用于主要的 INSERT 操作，但没有说明OUTPUT<的顺序是什么/代码>.所以，我不会试图依赖它.使用 MERGE 或添加额外的列来显式存储 ID 之间的映射.
the rows in the OUTPUT stream are not ordered. At least, strictly speaking, ORDER BY in the query applies to the primary INSERT operation, but there is nothing there that says what is the order of the OUTPUT. So, I would not try to rely on that. Either use MERGE or add an extra column to store the mapping between IDs explicitly.
这篇关于将 OUTPUT insert.id 与选定行的值相结合的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
