>Thank you. I can see how this approach can be useful in other cases (hence I will save this link for future). But in this case it won't work. The Identity values in the From database are smaller than the one in the To database. Therefore, even with the set identigy_insert off SQL will not allow it. I am going to start working on a VFP-way (a procedure) of doing it.

It is actually quite simple using OUTPUT clause of the INSERT command, e.g.

declare @t table (OldPK int, NewPK int) -- you can also add more columns

insert into myNewTable (col1, co2, col3, etc.)
OUTPUT Inserted.Pk into @t (NewPk)
select col1, col2, col3, etc. from oldTable Order By PK

;with cteNew as (select *, row_number() over (order by NewPK) as Rn from @t),
cte2 as (select (*, row_number() over (order by PK) as Rn from OldTable)

update t set t.OldPk =cte2.PK from @t T inner JOIN cteNew N ON T.NewPK = N.NewPK inner join cte2 ON N.Rn = cte2.Rn

----------------
Now you have oldPk and New PK, you can update your second table and insert into the table.