How do I alter my database to accomodate this new design?
===

I just finished some database changes and now I need to migrate the data to fit the new design.

We had a table of vehicles with a classification column. The classification column was a string someone typed into the field. It was not a foreign key column.

Now, I changed the design to allow a vehicle to have multiple classifications (yes, business rules allow this) and the classifications are moved to a lookup table. The classification column will be removed from the vehicle table, replaced with a M:M join table to enable the multiple categories per vehicle requirement. The classification table has been built using the unique values from the classification column in the vehicles table.

On to the question. How to I build the M:M table? What is the SQL to do this?

In my mind, the process would be: for each row in the vehicle table b) lookup the classification string in the new classification table c) insert the classification ID and the vehicle ID into the VehiclesClassifications table. Then, after checking the data, I'd manually remove the classification column from the vehicle table.

Thanks for any suggestions!