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.

  • Are you transferring the old keys to the new schema? If not, is it an option to do it, even if only for the duration of the import? – Rowland Shaw Dec 6 '13 at 9:05

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

| improve this answer | |

Maybe you could try a dictionary approach where you store a KeyValuePair for each mapping of an old key to a new one. Try converting one table at a time. Start with the tables which do not contain any foreign keys (i.e. make a partial order (http://en.wikipedia.org/wiki/Partially_ordered_set) on the table interdependencies)

Then you could read one row at a time from the first old table, get the key, insert the row into the new table and fetch the inserted id from the SQL insert command (SELECT SCOPE_IDENTITY() in SQL Server). Then you put the mapping from the old key to the new key as a KeyValuePair into a dictionary. You use this dictionary as a lookup for the next tables to come.

You have one dictionary per table. You could put the dictionaries into a dictionary mapping from table name to the old id/new id pairs, as well.

| improve this answer | |
  • This is definitely the way to go. It is worth noting that the dictionary tables should no longer be needed, once the data has been fully migrated. – Jørgen Fogh Jun 7 '15 at 10:49

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.