Unanswered: Synchronizing 2 tables with different field structure
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 attached a jpg file containing the 2 tables + sample data inside.
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.
I'm actually a PHP and DB amateur (a very inexperienced programmer to be precise) but co-erced to code this massive intranet project in PHP using PEAR:DB .... and am currently doing testing using MySQL as a db backend on my local workstation. And if everything goes well, I will have to migrate to Sybase later. Hence, I have to use generic sql codes to facilitate a more transparent transition between database types (from MySQL to Sybase or vice versa)....
Any solutions for the my sql code problem? The trouble is I really don't know where to start and how to start the sql statements in this scenario.... could you please start me off with the codes... then I can be on my own from there... just need some good samaritan to give me a good headstart.... thank you so much ;)
I really need help on this one... this forums are the only place I've got left to turn to. FYI, I'm the only lone developer for this project. Seriously need enlightenment.... and if you're referring to triggers as in triggerring the sync, the sync is done either automatically via a unix crontab or manually if the user clicks the "Sync Now" button on my php page in the browser.
I've only been advised by my project coordinator to use DECLARE CURSOR... to loop the entire procedure to read records from mcinfo table and do the sync with entityrelocation_tbl until all end of records. I'm still in a daze and have no inkling where to start coding the sql statements....
Nice to hear from you again. 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. 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.
By the way, just to enlighten you once again... 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.
** 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 **