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
I know I can use a GUID, is that the only option?
 
No comments:
Post a Comment