Monday, April 23, 2012

T-SQL: CTE with identity columns

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