[SOLVED] Daisy chain multiple scope

Daisy chain multiple scope_identities?

Let's say I have 4 tables and I want to use the the newly inserted pk from one table to the next (this is all executed as one batch)

declare @current_scope int;

insert into table1;
select t1.col;
set @current_scope  = select SCOPE_IDENTITY(); --this pulls table1's new row id

insert into table2;
select t1.col, t2.col from table1 t1 join table2 t2 where t2.ProductID = @current_scope ;
set @current_scope  = select SCOPE_IDENTITY(); --selects table2's new row

insert into table3;
select t2.col, t3.col  from table2 t2 join table3 t3 where t3.ProductID = @current_scope ;
set @current_scope  = select SCOPE_IDENTITY(); --selects table3's new row

insert into table4;
select t3.col, t4.col  from table3 t3 join table4 t4 where t4.ProductID = @current_scope ;

Is this possible?

Thanks

Solution

This my modified code to see how it was used:

insert into TableA 
(Id,
One,
Two) 
values
(@Id,
@One,
@Two)

SELECT @CopyofTableAId = SCOPE_IDENTITY()

/*************/
/*** STEP 2***/
/*************/
insert into TableB 
(ProductID,
PageName,
Notes,
Users) 
values
(@ProductId,
@PageName,
@Notes,
@Users)

SELECT @CopyofTaleBId = SCOPE_IDENTITY()

/*************/
/*** STEP 3***/
/*************/
insert into TableCombine 
(PKID1,
PKID2,
Etc) 
values
( @CopyofTableAId,
@CopyofTaleBId,
@Etc)