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