sql server - SQL Insert a record and use its Id to update another set of records foreign key -

i have 2 tables combinableorders , orders , tempory table of order ids

orders contain nullable fk combinableorders

i create record follows

insert    combinableorders ([rank])    values (0)

i need associate new combinableorder set of orders derived temporary table of ids

update orders   set orders.combinableorder_id = @id_to_original_insert   orders orders   inner join @ids ids   on orders.id = ids.id

how id new created combinableorders?

you want declare variable of right type

declare @id int

and set using scope_identity() after insert

select @id = scope_identity()

alternatively output in insert using output clause, that's may more complicated in case.

GCM