easiest way to map ids during database refactoring

Question

i have a number of tables with a column called OrderId. I have just done a refactoring and i want to get rid of the Order table and i have a new table called Transaction. I want all tables that have an OrderId column to now have a TransactionId column

This is complete. I now need to populate the transactionId column. I have a mapping today between orderId and transactionId so i wanted to see the quickest way i can go populate that new transactionId column (should i do this through code, through a SQL query, etc ??)

So i have the transationId column in the Order Table so i can do a join.

I want a query that says something like this (pseudo SQL)

update childTable CT
set transactionId = MapFromOrderId(CT.OrderId)

any suggestions?

Answer 1

I would do it in SQL code:

UPDATE MT
SET
    transaction_id = MAP.transaction_id
FROM
    My_Table MT
INNER JOIN My_Map MAP ON
    MAP.order_id = MT.order_id

Then check to make sure that every row was mapped:

SELECT
    *
FROM
    My_Table
WHERE
    transaction_id IS NULL

Answer 2

The process is usually:

• Make sure the database is backed up
• Addtransctionid to each child table. Populate based on a join to the mapping table (you did store the mappings between orderid and transactionid in a table?)
• Make sure you have no blank values.
• Then you create the FK for transactions, drop the fk to the Order table and then drop the orderid column.
• Then move to the next table and repeat.
• Test to make sure everything worked properly

Definitely I'd do this in a script so it will be easy to port to prod after dev and QA testing.

On prod you need to do this while the database is in single user mode to prevent new orders from being added as the process transitions.