SQL copying data with new foreign keys

I have a very tricky problem here. I have two tables. One is dependent on another by foreign key. Table 1

Table1ID	Instance ID	ModifiedBy
1	1	yevhen
2	1	yevhen
3	1	yevhen

Table 2

ID	Instance ID	Table1ID
1	1	1
2	1	2
3	1	3

These are taken as datasets for something named “Scenario”. When somebody creates new scenario, it triggers two stored procedures – one for Table1 and than one for Table2 which are basically replicating this data in the same table with new Instance ID. And that is where we have a problem. Table1 will autoincrement it`s Keys but Table2 will remain with old foreign keys for new instance like this

Table 1

Table1ID	Instance ID	ModifiedBy
1	1	yevhen
2	1	yevhen
3	1	yevhen
4	2	yevhen
5	2	yevhen
6	2	yevhen

Table 2

ID	Instance ID	Table1ID
1	1	1
2	1	2
3	1	3
4	2	1
5	2	2
6	2	3

How to make Table1ID setted with new ID`s from Table1?

Answer

You say you have two stored procs. But with simultaneous logic like this you should probably do it in the same stored proc.

In that proc, when inserting into table 1, use output to capture those newly inserted records. Those results will give you the inserted primary keys, which you in turn pump into table 2.

create table #newRecords (table1id int, [instance id] int);

insert      table1
output      inserted.Table1ID, inserted.[Instance ID] into #newRecords
select      [instance id], modifiedBy 
from        table1;

insert      table2
select      nr.[instance id], nr.table1id
from        #newRecords nr;

Your table 2 results from this would be as follows:

ID	Instance ID	Table1ID
1	1	1
2	1	2
3	1	3
4	1	4
5	1	5
6	1	6