Copy Rows with PK feedback loop

Question

Given the following (Table1):

Id    Field1  Field2     ...
--    ------  -------
NULL  1        2
NULL  3        4
...

I'd like to insert the values of Field1 and Field2 into a different table (Table2). Table2 has an auto increment integer primary key. I want to retrieve the new PKs from Table2 and update the Id column above (Table1).

I realize this is not conventional - its not something I need to do regularly, simply one-off for some migration work. I made some attempts using INSERT INTO, OUTPUT, INSERTED.Id, but failed. The PKs that are "looped-back" into Table1 must tie to the values of Field1/Filed2 inserted.

Answer 1

You should just be able to do a insert, then a delete and re-insert.

create table t1
( id int, f1 int, f2 int);

create table t2
( id int primary key IDENTITY , f1 int, f2 int);

insert into t1 (id, f1, f2) values (null, 1, 2);    
insert into t1 (id, f1, f2) values (null, 3, 4);
insert into t1 (id, f1, f2) values (null, 5, 6);
insert into t1 (id, f1, f2) values (null, 5, 6);

insert into t2 (f1, f2) 
select f1, f2 from t1 where id is null;

delete t1 
  from t1 join t2 on (t1.f1 = t2.f1 and t1.f2 = t2.f2);

insert into t1
select id, f1, f2 from t2;

select * from t1;

See this example on SQLFiddle.