0

I have two questions relating to migrating SQL data:

1. Migrate increment ID from db1.table to db2.table (different database)

Example:

enter image description here

My nightmare is when I have to move data for these 3 tables from one database to another database. The id in two database is not identical.

For now, my best practice is after migrating Guest and Room tables, I will use Excel with Vlookup to update all GuestId and RoomId in table Booking with new ID and after that migrating all these modified data.

Question: are there any better ways to go through this nightmare?

Please note that in this example, only 3 tables mentioned but in my real situation, it is not less than 5 tables joining together...

2. Migrate from IncrementId to Guid

For new project design, I'm using unique Guid instead of traditional increment ID for the table's primary key.

Question: What is the best practice to migrate old SQL Server table from incremented Id to Guid?

EDIT: I'm using SQL Server 2012 and can even use Entity Framework to access my database. So as long as it can help, SQL script or C# programming code are both useful

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Jacky
  • 2,924
  • 3
  • 22
  • 34
  • @marc_s Thanks for editing my question. Do you have any suggestion on my issue? – Jacky Sep 26 '16 at 10:38
  • other than saying: *don't do this!* - no, sorry. I think GUID's as PK in your SQL Server tables are a horribly bad idea - but it seems you're absolutely committed to going down that path, so I just shut up and let you make your own experiences with this ..... – marc_s Sep 26 '16 at 10:43
  • The first question is client required so I can't avoid it. But for my second question, i'm reading your post about GUID: http://stackoverflow.com/a/11938495/3133518. Thank you anyway – Jacky Sep 26 '16 at 10:54
  • I would: 1. Add guid to models and annotate them as ids. 2. Migrate, make sure it works. 3. Delete id, name guid field as id. 4. Migrate, make sure it works. Done. – Stan Sep 26 '16 at 13:31

1 Answers1

0

You could add an extra column to the target database as an int ExternalID (or similar) and copy the original int identifier into that column. Your migration process then needs to refer to that column.

Ben J. Boyle
  • 306
  • 1
  • 12