I have two databases and I have to move data from approx 34 tables from one db to another. Both dbs have the exact same structure. This work will be accomplished via stored procedures and a c# windows service.
The first part of the solution is to copy the necessary table data from the source db to a set of staging tables in the target db. The second part of the solution is to copy the data within the target db from the staging tables into the live tables. The staging tables have the same structure but no identities or fk constraints.
This is where I'm having some trouble figuring out the best way to do it.
Each table has an identity (bigint) and one or two foreign keys (bigints). The identity and fk values were all good in the source db, but as rows are inserted into the live tables from the staging tables the identity columns are assigning new values. This is causing a cascade of problems because I have to maintain a mapping table for each table to track the old vs the new Id's on the fly.
So for each table to process I would have to iterate over each row, save it to the the live table, then write a table specific mapping row with the old Id and the new id. So if each table to process also has an id mapping table I'm now up to 68 tables.
When finished all the newly inserted rows should relate to each other the same as the source db they came from even though their actual id values will be different.
This seems like a very 'brute force' method of doing this.
Does anybody have any experience doing anything similar, or know of a methodology that would help ?
Personally I think it may be a better design choice to have used GUIDs instead of integers for the primary keys. This way we could just blindly copy a bunch of rows between tables in the different dbs and it should work without having to change/remap any ids.
But as of right now we have integer ids. Other than changing our design to use GUIDs does anybody have any ideas that would make this process less painful ?