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?

score 3 · Accepted Answer · edited Jan 18 '21 at 12:30

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

score 2 · Answer 2 · edited Nov 03 '17 at 12:11

2

@@identity shows you actual identity value for your session.

You can't use CTE with IDENTITY FUNCTION but you can use temp table:

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

If you want to use CTE:

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

edited Nov 03 '17 at 12:11

Ajay2707

5,690
6
40
58

answered Apr 23 '12 at 10:52

devarc

1,157
1
7
11

row_number() has undefined behavior for a recursive query. – Bas Apr 23 '12 at 13:24
1

Hmm I didin't know about that. That's strange. I cannot find better/other option. So probably you have to use NEWID(). – devarc Apr 23 '12 at 13:44
Bas, in my team we have been using row_number() in CTE for a lot of time and various implementations. We have not found any problems. What exactly do you mean? – Vladislav Oct 17 '13 at 07:18

T-SQL: CTE with identity columns

2 Answers2

Linked