Synchronizing 2 tables with different field structure

Greetings everybody,


Currently I'm trying to sync Mcinfo table and another table called EntityRelocation table. Basically these two tables are somewhat the same... the Mcinfo table being the master table and the EntityRelocation table being the secondary table. Theoretically, i am supposed to replicate any changes made in Mcinfo table directly into EntityRelocation table... provided that the fields for both tables are the same. However in this case, it is NOT...



Here's what's going on >>

1) We have equipment/machines labelled with invidual names defined in Mc_No field (e.g. SA23)

2) When we relocate a machine to another section within the area, we change the names of the machines (e.g. changing SA23 to SA19, SA19 to SA08 etc.)

3) However, the technicians who perform the actual relocation process will have to update McInfo table first.

4) But when it is time for data synchronization, all updated data would be replicated into EntityRelocation_tbl, which has a different data structure as compared to McInto table ... as you can see from the tables above.

** The EntityRelocation_tbl above is displaying the desired results after both tables have successfully synched.


The trouble I'm facing now is... how do I write the generic sql code to perform synchronization of tables having different field structures as depicted above? This is the first time I've encountered such a scenario coz all this time, I've been only inserting/updating data between tables having same field structure which is pretty straight-forward.

Please help... thank you so much :)


=)
Nick