Data Migration/ Refrential integrity 1

[Fampofo]

Hi Guys,
I am using DTS to migrate a large data to SQL2000. We wish to change all PK's to computer generated no.keys during migration. How can I also change all the FK's to match the new prim keys. I mean is it possible or I have to stick with the old primary keys to ensure referential integrity is maintained?

 

[dds82]

Do this in x steps:

1) Add a column for the new primary key to your tables, data type "Long Integer". Set the "Auto Increment" option to "Yes". DO NOT delete the old keys.
2) Set this column as the primary key for each table you added it to. This will remove your old primary key settings, but that doesn't matter.
3) In each table that contains a foreign key, for each foreign key, add a new column with data type "Long Integer". This will be your new foreign key. Again, DO NOT delete the old keys.
4) Run an UPDATE query on all the tables with foreign keys to set the values of the new keys. The WHERE clause should specify matching relationships based on your old keys.
5) Delete the old primary and foreign keys from your database.
6) Rename your new foreign key columns to match their old names.

Good luck, this is a VERY tedious task... ;-)

 

[dds82]

Do this in 6 steps:

1) Add a column for the new primary key to your tables, data type "Long Integer". Set the "Auto Increment" option to "Yes". DO NOT delete the old keys.
2) Set this column as the primary key for each table you added it to. This will remove your old primary key settings, but that doesn't matter.
3) In each table that contains a foreign key, for each foreign key, add a new column with data type "Long Integer". This will be your new foreign key. Again, DO NOT delete the old keys.
4) Run an UPDATE query on all the tables with foreign keys to set the values of the new keys. The WHERE clause should specify matching relationships based on your old keys.
5) Delete the old primary and foreign keys from your database.
6) Rename your new foreign key columns to match their old names.

Good luck, this is a VERY tedious task... ;-)