Transfer data from one database to another database with different schema

Question

i have problem to Transfer data from one sqlserver 2008 r2 to another sql server 2012 databases with different schema, here is some different scenario,

1. database 1

database 1 with tables Firm and Client, these both have FirmId and ClientId primary key as int datatype, FirmId is int datatype as reference key used in Client table.

2. database 2

database 2 with same tables Firm and Client, these both have FirmId and ClientId but primary key as uniqueidentifier, FirmId is uniqueidentifier datatype as reference key used in Client table.

3. problem

the problem is not to copy data from 1 database table to 2 database table, but the problem is to maintain the reference key's Firm table into Client table. because there is datatype change.

i am using sql server 2008 r2 and sql server 2012

please help me to resolve / find the solution, i really appreciate your valuable time and effort. thanks

Answer 1

I'll take a stab at it even if I am far from an expert on SQLServer - here is a general procedure (you will have to repeat it for all tables where you have to replace INT with UID, of course...). I will use Table A to refer to the parent (Firm, if I understand your example clearly) and Table B to refer to the child (Client, I believe).

1. Delete the relations pointing to Table A
2. Remove the identity from the id column of Table A
3. Create a new column with Uniqueidentifier on Table A
4. Generate values for the Uniqueidentifier column
5. Add the new Uniqueidentifier column in all the child tables (Table B)
6. Use the OLD id column to map your child record & update the new Uniqueidentifier value from your parent table.
7. Drop all the id columns
8. Recreate the relations

Having said that, I just want to add a warning to you: converting to UID is, according to some, a very bad idea. But if you really need to do that, you can script (and test) the above mentioned procedure.