SQL Refactoring - Unique Names to UniqueIdentifiers

Question

I am in the midst of refactoring my database to support an upgrade to our web-based tool.

Imagine that I have four tables: Milestones, Categories, Skills, and PayRates. Under the old schema, each of these tables only listed a name, and that name was the key for the table.

Under the new schema, each table has not only a name, but also a generated uniqueidentifier that serves as the key for the table.

Now, also imagine that I have a table Tasks, where each Task consists of a name, a Milestone, a Category, a Skill, and a PayRate, and each of these is selected from their respective tables. Under the old schema, this table only stored names. Under the new schema, this table will store the IDs for the four tables instead of the names, like the following:

TaskID  TaskName  MilestoneID  CategoryID  SkillID  RateID

where TaskID is a generated uniqueidentifier for that Task.

Each of these tables currently contains data that needs to be transferred from the old schema to the new schema. I can assume that names of each of the four components of Tasks, and the names of Tasks themselves, are unique in the old schema.

My question is, what is the simplest query to move the data from the old schema into the new one?

This is being done to support storing two separate lists of Milestones, Tasks, etc. in the same database.

Answer 1

I'd do the following

• Step zero: Backup your database

• Step one: Add the unique identifiers to Milestones, Categories, Skills and PayRates. This is a simple column addition with default value the next id. This will generate your identifiers for the existing names.

• Step two: Add the four new columns to the existing task table (and add foreign keys to the source tables if desired), without deleting the old, name pointing columns.

• Step three: Run (assuming the old, name pointing columns are called Milestone, Skill and so on)

    update Tasks set MilestoneID = (
                                    select MilestoneID from Milestones where 
                                    Milestone = Tasks.Milestone
                                   ), 
                     CategoryID = (
                                    select CategoryID from Categories where 
                                    Category = Tasks.Category
                                  ),
                     SkillID = (
                                    select SkillID from Skills where 
                                    Skill = Tasks.Skill
                                  ),
                     PayRateID = (
                                    select PayRateID from PayRates where 
                                    PayRate = Tasks.PayRate
                                  )
  

• Step four: Check everything is in place

• Step five: Delete old columns from Tasks table, make new fields non-null