I need to migrate an old database's data to a new database with a new design. One major problem for me is that the old database uses nvarchar type as the primary key in its table for some reason. In the new design we have ints as keys with autoincrement. So far so good but my problem comes when I'm supposed to read the old data and keep relations correct.
Let's say I read data from one table old_A and inserts it to the new database table new_A with int keys. If I then read data from a table old_B and wants to insert some of that data into table new_B my problem comes if there should be a relationship between table new_A and new_B. I have no idea "which rows belong" to each other now, because the keys has changed.
I hope you can understand what I mean here, otherwise I need to explain better. But is there any recommendations on how to solve a problem like this? I'm not sure how to accomplish this migration and keep the data and its relations in a correct state. Do I need to keep the old nvarchar key as extra columns during the migration and then remove them after the migration is done to get this to work?
Right now I trying to do the migration from code in a C# program.
Best Answer
I have done this kind of job. You need to keep an extra column in your new table to keep old key value in that let say
TableA: id | Name | value
Then in new db it will be like
NewTableA: id | Name | value | old_id
that way you can keep track of all your tables but you also need to change some of your queries for this to check for old_id column.
hope it helps