[UPHPU] Database Merge

Jonathan Duncan jonathan at bluesunhosting.com
Sat May 22 20:20:48 MDT 2010


I ended up just doing a quick script to find duplicates, and then deleting duplicates.  Then I exported to csv with "select into outfile" every field but the "id" then imported from csv every field but the "id" so that the imported data would get new id, then I used the new id's on the secondary table which ended up not having as many as I initially thought there would be.  Thanks all for the input.



On 19 May 2010, at 18:28, William Attwood wrote:

> Ahhh, a section I didn't include - what if the values are already in the new database's secondary table with different ID #'s, you'd have to check by the VALUE in each array and find out which ID's are different and assign those accordingly - that's a few steps which should be done prior to creating the new array of values not in the new database.
> 
> 
> On Wed, May 19, 2010 at 6:26 PM, William Attwood <wattwood at gmail.com> wrote:
> Jonathan--
> 
>    Let's outline the scenario, you have two databases, four total tables (concept applies for more):
> 
> 	• Database1
> 		• Table1-1
> 		• Table2-1
> 	• Database2
> 		• Table1-2
> 		• Table2-2
> Your process is as follows (PHP Script):
> 
> 	• SELECT * FROM Database1.Table2-1 into ARRAY1 (id=>val)
> 	• SELECT * FROM Database2.Table2-2 into ARRAY2(id=>val)
> 	• INSERT INTO ARRAY3 (ALL FROM ARRAY2 NOT IN ARRAY1)
> 		• FOR EACH ELEMENT PUT INTO ARRAY3, ASSIGN ARRAY1[id][newid] = newid
> 	• SELECT * FROM Database1.Table1-1 into ARRAY4
> 		• FOR EACH ELEMENT IN ARRAY4 (data to merge into Database2) GET ARRAY1.referenceid (Table2-1)
> 		• SET ARRAY4.referenceid = ARRAY2[id][newid] (Table2-1->Table2-2)
> 	• INSERT INTO Database2.Table2-2 ARRAY3 ELEMENTS
> 	• INSERT INTO Database2.Table1-2 ARRAY1 ELEMENTS
> 
> This way you get all new values and new ID's for them in Step3, you keep the old ID and new ID in Step3.1, you then get all data from your Database1 table with those referential links, you update those according to the newid, and then insert all new data into your second database.
> 
> Did I miss anything? I'm in a hurry, I hope that's easy to understand.  I could have named the Arrays better.  
> 
> -Will
> 
> On Wed, May 19, 2010 at 5:53 PM, Jonathan Duncan <jonathan at bluesunhosting.com> wrote:
> UPHPU,
> 
> Here is a database conundrum.  I have two databases with identical tables (same structure different data).  They have been on different sites though collecting data from different groups of people.  I now need to merge that data.  There is one table that relies on the 'id' key of another table.  So the id's well need to be changed, and the other table updated to match.  Any ideas?
> 
> I think I like this way the best:
> http://www.everymanhosting.com/forum/about22.html
> 
> However, this will not address the required cascading update.
> 
> This has some good information:
> http://blog.mclaughlinsoftware.com/2009/05/25/mysql-merge-gone-awry/
> 
> But also not a final solution.
> 
> I am thinking I may just write a PHP script that will select from db1.table1, insert it into db2.table1 and then check if there is an associated row in db1.table2 and if so insert that in db2.table 2 with new 'id' key from last insert on db2.table1.
> 
> Thoughts?
> 
> Thanks,
> Jonathan (aka SunSparc)
> 
> 
> 
> 
> _______________________________________________
> 
> UPHPU mailing list
> UPHPU at uphpu.org
> http://uphpu.org/mailman/listinfo/uphpu
> IRC: #uphpu on irc.freenode.net
> 
> 
> 
> -- 
> Take care,
> William Attwood
> Idea Extraordinaire
> wattwood at gmail.com
> 
> 
> 
> -- 
> Take care,
> William Attwood
> Idea Extraordinaire
> wattwood at gmail.com



More information about the UPHPU mailing list