T-SQL：带有标识列的CTE [英] T-SQL: CTE with identity columns

问题描述

我正在构建一棵树（物料清单样式），并转换一些数据。请考虑下表：

I'm building a tree (bill of materials style), and transforming some data. Consider the following table:

BillOfMaterials

BillOfMaterials

• BomId


• ParentId

现在我正在使用CTE来填充它：

Now I'm using a CTE to fill it up:

with BOM as 
(
select @@identity as BomId, null as ParentId <some other fields> from MyTable
union all
select @@identity as BomId, 
       parent.BomId as ParentId,
       some other fields
from MyTable2
inner join BOM parent on blabla)

insert into MyTable3
select * from BOM

问题是：@@身份只会提供我在联合之前插入的最后一条记录的身份。

Problem is: the @@identity will only give me the identity of the last record inserted before the union.

我该怎么办才能获得身份？我可以修改Table3，但不能修改Table1或Table2

What can I do to get the identity? I can modify Table3 but not Table1 or Table2

row_number（）具有递归查询的不确定行为，所以我不能在这里使用它。

row_number() has undefined behavior for a recursive query, so I cannot use it here.

我知道我可以使用GUID，这是唯一的选择吗？

I know I can use a GUID, is that the only option?

推荐答案

您无法在CTE中捕获生成的身份。但是，您可以使用 null 作为 ParentID 将所有行插入目标表，然后更新 ParentID 在单独的更新语句中。为此，您可以使用 merge 和描述的技术。

You can't capture the generated identity in the CTE. You can however insert all rows to the target table with null as ParentID and then update ParentID in a separate update statement. To do that you can use merge and a technique described here.

-- Helper table to map new id's from source
-- against newly created id's in target
declare @IDs table
( 
  TargetID int,
  SourceID int,
  SourceParentID int
)

-- Use merge to capture generated id's
merge BillOfMaterials as T
using SourceTable as S
on 1 = 0
when not matched then
insert (SomeColumn) values(SomeColumn)
output inserted.BomId, S.BomID, S.ParentID into @IDs;

-- Update the parent id with the new id
update T
set ParentID = I2.TargetID
from BillOfMaterials as T
  inner join @IDs as I1
    on T.BomID = I1.TargetID
  inner join @IDs as I2
    on I1.SourceParentID = I2.SourceID

此处是 SE上的完整示例-数据

这篇关于T-SQL：带有标识列的CTE的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！