I have 3 tables.
SourceTable - a source table with some data in it
DestinationTable - a destination table with the same schema as the Source table. both tables have similar kind of data
FKSourceTable - a totally different table that has a FK reference to SourceTable
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
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