Search code examples
sql-servert-sqlforeign-keys

Maintaining Foreign Key references while migrating data using T-SQL

I have 3 tables.

  1. SourceTable - a source table with some data in it

  2. DestinationTable - a destination table with the same schema as the Source table. both tables have similar kind of data

  3. FKSourceTable - a totally different table that has a FK reference to SourceTable

  4. FKDestinationTable - a totally different table that has a FK reference to DestinationTable. Has the same schema as FKSourceTable

Now I'm given the task of migrating some of the data from the SourceTable to the DestinationTable and FKSourceTable to FKDestinationTable

However I cannot migrate Primary Keys as the DestinationTable may have its own records with the same PK and that might create a PK violation.

DestinationTable as an Auto Identity column for the PK and when I do a Bulk insert, I don't specify the PK column so Auto Identity will do its Job.

This means the new records in DestionationTable will have brand new IDs.

The problem I'm having is, how do I maintain the FK reference when migrating FKSourceTable to FKDestinationTable? When I do a bulk insert to DestinationTable as follows, I lose track of the Identities:

INSERT INTO DestionationTable
    (Col1, Col2)
SELECT st.Col1, st.Col2
FROM SourceTable st

(DestionationTable has 3 columns: Id, Col1, Col2)

The challenge is that I cannot use SSIS or any other ETL solution. I need to be able to do this with a simple SQL Script.

Does anyone have any ideas to tackle this? I've tried using OUTPUT INTO etc. but I haven't figured out a way to keep a reference between the original Id and the new Id

Any help is greatly appreciated

Solution

  • This is probably not the most optimal solution but it should get the job done.

    Idea is to disable identity insert and generate IDs yourself based on what is already in the table.

    What this does is it iterates through source data and inserts it into destination tables one row at a time.

    Please review this code thoroughly before executing because I didn’t test this myself

    declare @col1 varchar(20)
declare @col2 varchar(20)
declare @col3 varchar(20)
declare @new_id int

set identity_insert on

declare source_data cursor for
select col1, col2, colx
from SourceTable

open source_data

fetch next from source_data 
into @col1, @col2, @col3

WHILE @@FETCH_STATUS = 0
begin
  set @new_id = select MAX(ID) + 1 from SourceTable

insert into DestinationTable (ID, col1, col2, col3) values (@new_id,@col1,@col2,@col3)
-- do something similar for FKDestinationTable
insert into FKDestinationTable (ID, col1, col2, col3) values (@new_id,@col1,@col2,@col3)

fetch next from source_data
into @col1, @col2, @col3 
end 

set identity_insert off