Moving products from one cart to another

[danthman]

I want to move the products from one cart to another. I can copy the necessary database tables from the old cart such as categories_description, products_description, etc., but how do I get it into the new cart. The new cart has other products in it so that the product ID and category ID of the old cart will conflict with the IDs of the new cart. How can I format the MySQL data to import it successfully into the new cart database?

 

Looking at the two databases, there are about 85 products in each cart. If I add a 1 to each category ID, product ID and other necessary IDs, in other words changing the product ID of a product in the old cart from 56 to 156, will this work? What numbers will I need to change to use this method? Is there a better method?

Thanks for your help,

 

Dan

[Jack_mcs]

Install the Easy Populate addon into both shops. Be sure to have unique model numbers on the new products to be uploaded.

[MrPhil]

If I understand your question, you want to take some (or all) of the products in one osC store installation ("cart") and import them into another osC store installation ("cart")?

 

I think if you export your database into an .sql file, you could edit that file to remove all but the table data of interest (get rid of customer data, country lists, etc.). Remove any SQL commands to create tables, after confirming that the old and new systems have the same table structure (fields). You should now just have INSERT INTO commands left. If you end up directly running SQL queries to manually insert new rows, it would be a good idea to first back up the whole (second store) database, in case something goes wrong!. You should also shut down the store while fooling with the database.

 

It might be possible to do as you asked and just arbitrarily assign new IDs by adding 100 to the ones being imported, but I won't swear that your particular version of MySQL will like having you assigning values to 'autoincrement' fields. Go ahead and try it -- hopefully the worst that will happen is that you end up having to restore the backup of the new store. :-( If MySQL balks at doing this, it might work if you ALTER all table definitions to temporarily remove the 'autoincrement' attribute, and restore it when done. Don't forget to update all the places where a table's ID is used in other tables before doing the import. Import the .sql file so that rows are INSERTed. When done, test that autoincrement is starting in the right place for any subsequent product adds. Add a test product when you're all done, and see if its product ID is the highest existing ID + 1. If not (it's resuming at the old place), you might have to manually ALTER each table to set a new AUTOINCREMENT starting value. I haven't played this deeply in MySQL, so I don't know what it's going to do... I just want to give a "heads up" on possible problems!

 

If MySQL won't let you import new records with fixed (arbitrary) key values (autoincrement fields), you've got extra work ahead of you. Look for "autoincrement" fields (usually product ID, etc.) and change them to null so that a new ID will be generated. For each ID (autoincremented key value), figure out what the new ID is going to be (starting at 1 + previous highest value) and update all references to the old key to this value. Import the .sql file.

 

What might be far easier is to take the .sql dump of the old products database tables, and for each product, cut and paste the various bits of data into the admin form to enter a new product. In other words, grab the data and create products the normal way, rather than fooling directly with the database. That way, there's less chance of overlooking some ID that got changed. It's still a fair bit of labor, but at least you won't have to shut down the store while manually mucking around with the database, and it's low risk.

 

I presume that there hasn't been much of this kind of activity (merging one store into another), or someone would have come up with a utility to take the old store's .sql file and automate the entry of the product record, get the resulting new ID, and propagate it to related records as it inserts them into the database (or goes back and updates existing records if it can't figure out a sequence to do it in). If you've got more than a few hundred products, that might actually be worth the effort (faster than doing it all manually). You said you have only about 85 products, so if this is a one-time deal, I'd just do it manually and get it over with.

 

Whatever way you do it, don't forget to bring over product image files, manufacturer information, new categories (and their images), and whatever else is needed for the new products. Good luck!

[danthman]

After our discussion I decided to try the method we discussed. I copied the dump of the products, products_description and products_to_categories. Using find and replace I added 100 to each product ID in all three tables. There were no attributes to deal with. I then manually set up the new categories in the new cart using the shopping cart admin console to match the category layout of the old cart. Using find and replace I updated all the category IDs in the products_to_categories table to match the new category_IDs. After backing up the database I uploaded the new products and uploaded the images. It worked like a charm, took about 45 min. I did it for 85 products but it would not take much longer if there were 100's of products.

 

Thanks for your advice!