combining multiple databases

[Guest]

hiya!

 

is there a way to combine 8 osc databases into one?

 

i was asked to combine 8 of my store databases - total products of over 5000. i would really not like to do this one by one.

 

i have searched the contributions for anything that could help me - but i cant find any that would help.

 

the problem i am having is that the id's are clashing - the ids on each database is 90% the same.

 

i would have to change the id's for the products, categories and options/attributes.

 

we have tried to build a script, and it works... sorta. after we compiled the database there were products mssing, and the options for the products were wrong.

 

we'v been struggling with this now for weeks - and i'm now at the end of my rope.

 

please - if anyone can help me - i would greatly appreciate it.

[Guest]

There is a bunch of associated tables need to be in synch before you merge the dbases. So before merging I would modify the ids of one dbase and test the script.

 

1. Find the tables using the products_id column

products

products_attributes

products_descriptions

products_to_categories

products_notifications

order_products

customers_basket

Maybe more and you could make a script to isolate the tables with the products_id column from each dbase. (make sure each dbase has the same number of product_id and the same tables.

2. Defrag the ids so they are sequential, setup another script for it to update each table

3. For each dbase set the next products id to be assigned as the base for the next dbase products_id.

4. Merge the tables.

 

Also before starting make sure the dbases have a good integrity because if just one id is missing from one table the entire process may fail.

[♥Monika in Germany]

I suggest a different approach, leaving you with "holes" in the id sequence but you probably do not need sequential ids right? Are we talking about only merging products, but not orders/customers?

 

start like enigma suggested, find ALL table using the products_id. For that I suggest a table export (just structure) and find the tables in an editor.

 

then double check that in all databases the language ids are the same ... it would be no fun to find english text in the french version.

 

Now add a new column to all these tables, calling it new_products_id. It should be in the second position in the table for easy viewing. You will have to do this for all databases apart from the one you plan to use as the new "master database", the only one that is allowed to keep the ids. Fill the new column in merge database 1 with the value 10.000 + products_id, in merge database 2 with 20.000 + products_id, repeating for all databases apart from the master one and for all tables in one database. So all tables in database one would have the new_products_id exactly 10.000 more than before. As you said you will only be having 5000 products, this cannot clash.

 

When you are done, delete the original products_id column, and rename the new_products_id to products_id. No need to recreate the autoindex etc as you will only be exporting. Export each table and reimport to the master database. HTH

[Guest]

thanks a bunch :) i'll give both these suggestions a try and see what happens :)