Geek asks and answers

T-SQL: CTE with identity columns

I build a tree (material style) and transform some data. Consider the following table:

BillOfMaterials

  • Bomid
  • ParentId

Now I use CTE to populate it:

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

The problem is that the identifier @@ will only give me the identifier of the last record inserted before the join.

What can I do to get a personality? I can change table 3 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 this the only option?

+3
source share
2 answers

CTE. null ParentID, ParentID . merge .

-- Helper table to map new id from source
-- against newly created id 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

SE-Data

+3

@@identity .

CTE IDENTITY FUNCTION, :

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

CTE:

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

More articles:


All Articles