Results 1 to 7 of 7
  1. #1
    Join Date
    Oct 2003
    Posts
    20

    Question Unanswered: 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...


    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.

    Please help... thank you so much :)


    =)
    Nick
    Attached Thumbnails Attached Thumbnails table.gif  

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322

    Re: Synchronizing 2 tables with different field structure

    Originally posted by nickcwj
    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?
    You don't

    And how have you been doing it when they were the same?

    SELECT *?

    Ouch

    Happy coding.

    Are you using triggers btw?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Oct 2003
    Posts
    20

    Arrow

    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.

    Regards,
    Nick
    Last edited by nickcwj; 10-16-03 at 21:44.

  4. #4
    Join Date
    Oct 2003
    Posts
    20

    Red face

    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....

    Nick

  5. #5
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Originally posted by nickcwj
    I'm still in a daze and have no inkling where to start coding the sql statements....

    Nick
    When you have no inkling..start drinkling..

    And the project manager is giving you bad advice in using cursors

    Can you post your ddl?

    Can you indicate when the need to be synched, also are there any Rules about the synch?

    Sample DDL, DML and sample Data will help us help you...we can try it for our selves...other than that we can only guess..
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  6. #6
    Join Date
    Oct 2003
    Posts
    20

    Exclamation

    Greetings Brett,

    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 **

    Hope to hear from you soon... thanks :)

    Regards,
    Nick
    Attached Files Attached Files
    Last edited by nickcwj; 10-18-03 at 00:45.

  7. #7
    Join Date
    Oct 2003
    Posts
    20
    Excuse me, can anybody out there help me? I've been left idle for quite sometime now.... would really appreciate if some good samaritan would come my way and offer a helping hand.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •