5

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.

  1. 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.

  1. 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

jarlh
  • 42,561
  • 8
  • 45
  • 63
adnan
  • 1,429
  • 1
  • 16
  • 26
  • Is this a one off transfer or something that needs to run often? – Allan S. Hansen Jun 02 '16 at 07:15
  • its a one off transfer, basically we changed our schema old database 1 with int datatype as primarykey and new database 2 with uniqueidentifier datatype as primarykey, – adnan Jun 02 '16 at 07:23
  • uniqueidentifier is a bad idea for primary key as it will mess up the clustered index on the primary key – Diceyus Aug 21 '17 at 09:59

1 Answers1

4

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.

p.marino
  • 6,244
  • 3
  • 25
  • 36
  • This answer looks at the surface like it is going to work. I agree with you on not letting keys be uniqueidentifiers/guids. – David Söderlund Jun 02 '16 at 07:33
  • thanks for your answer, but the problem is at point 5 and 6. i have about 10k records in reference table, is there will be manually matching reference key and update key? – adnan Jun 02 '16 at 07:41
  • @adnan: no you are not supposed to do that manually, of course. When you have finished step 4 Table A will have old_id (int) and new_id (guid). Therefore for each Table B Record you can look up the new_id value using old_id as a reference. – p.marino Jun 02 '16 at 07:59