T-SQL：CTE与标识列(T-SQL: CTE with identity columns)

我建立一个树（材料风格法案）和转化一些数据。 请看下面的表格：

材料清单

• 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

问题是：@@身份只会给我的工会之前插入的最后一条记录的身份。

我能做些什么来获得身份？ 我可以修改表3而不是表1表2或

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

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

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-数据

Answer 2:

@@identity显示您的会话实际身份值。

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

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
)

文章来源: T-SQL: CTE with identity columns