T-SQL：带有标识列的 CTE

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

材料清单

• BomId
• ParentId

现在我使用 CTE 来填充它：

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

问题是：@@identity 只会给我在并集之前插入的最后一条记录的标识。

我可以做什么来获取身份？我可以修改 Table3，但不能修改 Table1 或 Table2

row_number()递归查询有未定义的行为，所以我不能在这里使用它。

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

您无法捕获 CTE 中生成的身份。但是，您可以使用以下命令将所有行插入到目标表中null as ParentID然后更新ParentID在单独的更新声明中。为此，您可以使用merge以及所描述的技术here https://stackoverflow.com/questions/5365629/using-merge-output-to-get-mapping-between-source-id-and-target-id.

-- 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-Data https://data.stackexchange.com/stackoverflow/query/67718