Maintaining Foreign Key references while migrating data using t-sql

Question

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

Thank you Nandun.

If you are on SQL Server 2008+, please take a look at the method discussed in [this thread](http://stackoverflow.com/questions/5365629/using-merge-output-to-get-mapping-between-source-id-and-target-id "Using merge..output to get mapping between source.id and target.id"). — Andriy M, Jun 21 '13 at 08:04

score 3 · Accepted Answer · answered Jun 20 '13 at 09:08

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.

Pls 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

thank you for this approach. how ever i'm worried about MAX(ID) + 1 because my data aren't exactly sequential. records get deleted every now and then and the MAX ID won't give the correct ID.. How ever I'm keen to use this approach with a IDENT_CURRENT instead of MAX(ID). Only thing is I was hoping to get this done without a cursor — Nandun, Jun 20 '13 at 15:07
although i didn't want to use cursors and loops, i WAS able to use this approach successfully. so marking this as the answer. — Nandun, Jun 24 '13 at 14:35

score 0 · Answer 2 · answered Jun 19 '13 at 22:59

Insert the data into the Destination table using Ident_Current of Destination table

    DECLARE @ID  INT = IDENT_CURRENT('DestionationTable')

    INSERT INTO DestionationTable
        (ID, Col1, Col2)
    SELECT @ID + ROW_NUMBER() OVER(ORDER BY st.ID),st.Col1, st.Col2
    FROM SourceTable st
    WHERE -----

Now you have information of what each ID in source table = which ID in destination table.

    SELECT @ID + ROW_NUMBER() OVER(ORDER BY st.ID) [NEW_ID], st.ID [OLD_ID]
    FROM SourceTable st
    WHERE -----

Note: Make sure this is done in a transaction and the transaction type depends on the usage of these tables

Maintaining Foreign Key references while migrating data using t-sql

2 Answers2