I'm looping through one table and inserting into another. The IDENTITY value for the target table is coming from a 3rd table. The code below is wrong but expresses what I'm trying to do. I need to insert into table1 first and use its IDENTITY value for col1 in table2. So, the table1 insert needs to move into the looping through table3. How can this be done?
insert into table1 (col1, col2, col3)
values(1, 2, 1)
insert into table2 (col1, col2, col3)
select SCOPE_IDENTITY(), 1, 1)