Unanswered: Synchronizing 2 tables with different field structure
I am coding a PHP/PEAR:DB-based intranet application... using generic sql codes (no proprietary syntax as evident in MySQL or Sybase)... currently using MySQL as the db backend for testing on my local workstation... will be migrating to a Sybase db server when everything is up and running like clockwork.
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...
** I have a php page with a "Sync Now!" button... whenever the user clicks this button, it will trigger all the sql codes to perform the table synchronization process **
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 ...
I have attached the sample SQL script, the db schema, and a diagram... all in a ZIP file. After you run the script, there will be sample data inside the mcinfo table. However, I left the entityrelocation_tbl table blank... to facilitate ease of inserting new fresh data during synch, and later comparing and updating the freshly inserted data when necessary.
>> The trouble I'm facing now is... how do I write the generic sql code from scratch to perform synchronization of tables having different field structures as depicted in the diagram (located in the attached zip file) ?
I just need to know how to code from scratch the synching process when entityrelocation_tbl is blank, and when entityrelocation_tbl already contains exisiting data.