6 {{ upvoteCount | shortNum }}
6 {{ upvoteCount | shortNum }}

Need help to combine mysql databases

by Rob Richards
5 replies
Hi Warriors,

This one is beyond me, how about you?

I use a script which stores info in a mysql db. I have used the script several times on different servers.

I now want to combine one of the tables from several servers into one table.

Any ideas or help would be appreciated.

Thanks

Rob

PS. The tables that I want to combine are identical, except for the information stored on them
#combine #databases #mysql
  • Profile picture of the author Sean Donahoe
    If the tables have the exact same structure that should not be a problem. It will depend if you have referenced index keys or primary keys which link tables with other data.

    Often times, I find it best to write a PHP script that opens all the items from the tables and place the items into an array. I then loop through the array and create a destination table to place all the data.

    I use the code to create a validation process for inserts or updates (for duplication checking etc) and any other checks needed.

    That way I can control the data process and combining of fields.

    Hope that helps.
    {{ DiscussionBoard.errors[25392].message }}
  • Profile picture of the author Sean Donahoe
    That would work only if there was no Primary keys or crosslinked references from other tables which is why I suggest creating a simple PHP filter to run against these tables. Your PHP script then becomes a funnel for all that data into the spout (destination table).

    If you need some pseudo code:
    Code:
    // Loop through each table 
    // Load in table x into Array
    // Start Loop of Array
        // Does record exist in destination?
        // Any other validation checks
        // Data integrity checks (such as crosslinked indexes)
        
        // Based on checks, insert, update or ignore
        
    // End Array Loop
// End Table Loop

// Report Summary
    {{ DiscussionBoard.errors[25413].message }}
    • Profile picture of the author Rob Richards
      Thanks Jeff and Chikira, I appreciate your responses.

      You've given me just enough to make me think I know what I'm doing and just enough to get myself into a lot of trouble. :-)

      I guess I'm still floundering with this whole idea.

      Any additional suggestions?

      Thanks

      Rob
      Signature

      Need Help With Your Google Places Results?
      Let Me Create 50/100 High PR Citations For You!
      Just $50/$100 - PM Me When You're Ready

      {{ DiscussionBoard.errors[26889].message }}
      • Profile picture of the author seasoned
        \1. *******IF******* the keys are natural keys, there will probably be, NO PROBLEM!
        2. *******IF******* the keys are globally unique synthetic keys, there will probably be, NO PROBLEM!
        3. IF, in the VERY likely event the keys are simply synthetic keys, there probably WILL be a problem!

        So the first 2 would be simply reading the data, and writing it to the target. The third would require modifying all related key fields that would otherwise be duplicates.

        Remember, even a nuclear reactor seems simple, but you can have a LOT of problems if you don't plan it right.

        Steve
        {{ DiscussionBoard.errors[28716].message }}
      • Profile picture of the author Andy Fletcher
        Rob. It all depends how complicated the database is. Do you know enough about MySQL to export one of the databases and attach the file to a post here so we can take a look?
        Signature
        {{ DiscussionBoard.errors[28873].message }}

Trending Topics