Insert and Relate Records in Single SQL Query?

Question

I am trying to copy data from an old database into a new one, and transform it to follow the new db structure. The old one looked something like this:

Table 1:

Id | Col A 
----------
1      0
2      8
3      7
4      1

In the new database, Table 1 now looks like this, where the data from col A is now in another table, and it's linked back to Table 1 via the A_Id foreign key column:

Table 1:

Id | A_Id_FK
----------
1      0
2      1
3      2
4      3

Table 2:

Id | Col A
----------
0      0
1      8
2      7
3      1

So...I'm very rusty with my SQL skills. I need a one time (read: speed doesn't matter) query to create the Table 2 records while simultaneously linking them to the Table 1 records. Another point to mention is that I have two databases. One is the old one with Table 1, and the other is the new one with an empty Table 2, and a Table 1 that has all the other info aside from the A_Id_FK, which for now is just linked to an empty record in Table 2 for each record.

Thanks in advance.

Edit - For some extra clarity, Table 2 in the new DB is empty. Col A in the old database is NOT a unique number (in fact, it's not a number at all, but it's just that way for simplicity). Table 1 in the new database is populated, but the value in A_Id_FK is set to 1 for everything just to have a valid foreign key. Lastly, even identical Col A records need their own instance in the new db.

Answer 1

Assuming databases "Old" and "New" with the table structures described above, assuming that both databases are present in the same SQL instance, assuming that every "ColA" value in the Old Table1 is present in New Table2, and assuming that you're loading the data for the first time, one version would be:

INSERT New.dbo.Table1 (Id, A_Id_FK)
 select Old.Id, New2.Id
  from Old.dbo.Table1 Old
   inner join New.dbo.Table2 New2
    on New2.ColA = Old.ColA

It sounds like you've perhaps already populated New..Table1? If so, you'd need some kind of update statement that links that table with Old..Table1 and New..Table2.

Answer 2

I'm assuming Id in Table2 is an identity in the code below.

insert into Table2
    (ColA)
    select distinct ColA
        from OldTable1

insert into Table1
    (Id, A_Id_FK)
    select ot.Id, t2.Id
        from OldTable1 ot
            inner join Table2 t2
                on ot.ColA = t2.ColA

Answer 3

You can use CURSOR (Table_2.id is identity):

DECLARE @id INT
DECLARE @col_a VARCHAR(50)
DECLARE db_cursor CURSOR FOR 
    SELECT id, col_a
    FROM Table_Source

OPEN db_cursor  
    FETCH NEXT FROM db_cursor INTO @id, @col_a

WHILE @@FETCH_STATUS = 0  
BEGIN  
    INSERT INTO Table_2 (col_a) VALUES (@col_a)
    INSERT INTO Table_1 (id, fk) VALUES (@id, @@IDENTITY)

    FETCH NEXT FROM db_cursor INTO @id, @col_a
END  

CLOSE db_cursor  
DEALLOCATE db_cursor