2

I am working on "cleaning up" a database and need to synchronize the IDENTITY columns. I am using stored procedures to handle the data and mirror it from one table to the next (after cleaning it and correcting the datatypes). At some point in the future I will want to cut off the old table and use only the new table, my question is how to have the IDENTITY field stay in sync while they are both in use... Once the old table is removed the new one will need to continue auto-incrementing and rebuilding/altering it to change the IDENTITY field is not an option. Is this possible or is there a better way to go about this?

My other thought was to create a lookup table to store the ID columns of both tables and anytime there is an insert in the new table take the old ID and new ID and insert them into the lookup table. This is kind of messy once the old table is out of the way tho.

jon3laze
  • 3,188
  • 6
  • 36
  • 69

2 Answers2

3

Been there, done that. Put the old id in the new table as an FK. Drop that column just before you drop the old table.

tpdi
  • 34,554
  • 11
  • 80
  • 120
  • i like this idea (+1). So before using youR NEW table, change the type to an identity field? – Mark SQLDev Dec 02 '10 at 19:14
  • +1 I like this idea, however I forgot to mention there would be records added to the new table that wouldn't be in the old table. My solution is to change the ID field in the new table to a non-identity field and put a trigger on the old table to populate the new. Then on cut-over I will change to identity. – jon3laze Dec 02 '10 at 19:28
  • No, no. The new table gets its own id field. Old or new records get the new id when they are inserted in the new table. new_table.old_id defaults to null, but when you copy records from the old table, you give old_id the value of old_table,id: insert new_table( c1, c2, old_id) select c1, c2, id from old_table; – tpdi Dec 02 '10 at 20:00
1
  1. Set the new table's identity to be a non-identity field.
  2. Modify either your data population procedures to populate the non-identity field on your new table with the old table's identity value.
  3. At cutover, switch your new field to auto-increment and set the seed number accordingly.
Bob Palmer
  • 4,714
  • 2
  • 27
  • 31