T-SQL: 带有标识列的CTE

Question

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

物料清单

• 清单ID
• 父级ID

现在我正在使用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 只会给我返回 union 前插入的最后一条记录的标识。

我该怎么做才能获得标识？我可以修改 Table3，但不能修改 Table1 或 Table2。

对于递归查询，row_number() 的行为未定义，因此我无法在这里使用它。

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

- Bas

Answer 1

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

-- 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上的完整可工作示例：

- Mikael Eriksson

Answer 2

@@identity 显示您的会话的实际标识值。

您不能在 IDENTITY FUNCTION 中使用 CTE，但可以使用临时表：

SELECT IDENTITY(int,1,1) AS  BomId, un.*
INTO #BOM
FROM <your union> as un

如果您想使用CTE：

with BOM as 
(
  SELECT ROW_NUMBER() OVER(ORDER BY <column> ) AS  BomId, un.*
  FROM <your union> as un
)

- devarc

---

row_number() 在递归查询中具有未定义的行为。 - Bas

嗯，我不知道那个。很奇怪。我找不到更好/其他的选项。所以你可能需要使用NEWID()。 - devarc

Bas，在我的团队中，我们已经使用CTE中的row_number()函数很长时间并且有多种实现方式。我们没有发现任何问题。你具体是什么意思？ - Vladislav