T-SQL: CTE with identity columns

Question

I'm building a tree (bill of materials style), and transforming some data. Consider the following table: BillOfMaterials - BomId - ParentId 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. What can I do to get the identity? I can modify Table3 but not Table1 or Table2 `row_number()` has undefined behavior for a recursive query, so I cannot use it here. I know I can use a GUID, is that the only option?

Accepted Answer

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
```

Here is full working sample on SE-Data

T-SQL: CTE with identity columns

Solution

Problem

Related problems