Transfer Orders/Customers from 1.5 deployment to 1.6

[OllyL]

Hi There,

 

We're in the process of building an snazzy new version of our store using the latest 1.6 Prestashop platform which will soon be replacing our existing store running on 1.5. The new version of the store was built using a system image of the live site (which was then upgraded and modified) and so most of our data is already correct however we will need to migrate all new customers and orders over from the current site which have come in since the work began.

 

What is the best way to do this? I have taken a look through the modules catalogue but I could not see anything for migrating orders, only customers and products. We don't use stock management or any custom plug-ins for products/suppliers etc.so it literally is just the order and customer information we need and nothing more.

 

Does anyone know of a way to do this?

 

Thanks very much,

 

Olly

[OllyL]

FYI

 

Not sure if this is the best way but have come up with a database query for doing this. Note that this is ONLY RECOMMENDED if you are migrating and old->new database as my post above and not recommended for copying data between two different shops or different platforms (it relies on the unique identifiers being the same). Also this script does not include the customer_messages table(s) as these have been modified in version 1.6 and since they are not so important for us I decided to leave them out.

 

I should add that "live" is the new, target database. "backup_211014" is the existing database containing the new records. @startid and @endid should be amended to include the start/end record that you wish to import in each case (either id_order, id_customer, or newsletter sub id).

 

Hope this helps someone, or if a [spam-filter] sees this post please let me know of problems or better solutions to this problem as I've not performed the migration yet (still exploring alternate options),

 

Cheers,

 

------------------------------

/*CUSTOMER MIGRATION SCRIPT */

BEGIN;

SET @startID = 38535;
SET @endID = 38536;

INSERT INTO live.`ps_customer` 
SELECT * FROM backup_211014.`ps_customer` 
WHERE id_customer >= @startID AND id_customer <= @endID
AND id_customer NOT IN (SELECT id_customer FROM live.`ps_customer` WHERE id_customer >= @startID AND id_customer <= @endID)
ORDER BY id_customer ASC;

INSERT INTO live.`ps_address` 
SELECT * FROM backup_211014.`ps_address` 
WHERE id_customer >= @startID AND id_customer <= @endID
AND id_customer NOT IN (SELECT id_customer FROM live.`ps_address` WHERE id_customer >= @startID AND id_customer <= @endID)
ORDER BY id_customer ASC;

INSERT INTO live.`ps_paypal_customer` 
SELECT * FROM backup_211014.`ps_paypal_customer` 
WHERE id_customer >= @startID AND id_customer <= @endID
AND id_customer NOT IN (SELECT id_customer FROM live.`ps_paypal_customer` WHERE id_customer >= @startID AND id_customer <= @endID)
ORDER BY id_customer ASC;

COMMIT;

/*ORDER MIGRATION SCRIPT */

BEGIN;

SET @startID = 36892;
SET @endID = 36892;

INSERT INTO live.`ps_order_slip_detail` 
SELECT backup_211014.`ps_order_slip_detail`.* FROM backup_211014.`ps_order_slip_detail` 
INNER JOIN backup_211014.`ps_order_slip` 
ON backup_211014.`ps_order_slip_detail`.id_order_slip = backup_211014.`ps_order_slip`.`id_order_slip`
WHERE id_order >= @startID AND id_order <= @endID
AND id_order NOT IN (SELECT id_order FROM live.`ps_order_slip` WHERE id_order >= @startID AND id_order <= @endID)
ORDER BY backup_211014.`ps_order_slip_detail`.`id_order_detail` ASC;

INSERT INTO live.`ps_order_detail_tax` 
SELECT backup_211014.`ps_order_detail_tax`.* FROM backup_211014.`ps_order_detail_tax` 
INNER JOIN backup_211014.`ps_order_detail` 
ON backup_211014.`ps_order_detail_tax`.`id_order_detail` = backup_211014.`ps_order_detail`.`id_order_detail`
WHERE id_order >= @startID AND id_order <= @endID
AND id_order NOT IN (SELECT id_order FROM live.`ps_order_detail` WHERE id_order >= @startID AND id_order <= @endID)
ORDER BY backup_211014.`ps_order_detail`.`id_order_detail` ASC;

INSERT INTO live.`ps_order_payment` 
SELECT backup_211014.`ps_order_payment`.* FROM backup_211014.`ps_order_payment` 
INNER JOIN backup_211014.`ps_order_invoice_payment` 
ON backup_211014.`ps_order_invoice_payment`.`id_order_payment` = backup_211014.`ps_order_payment`.`id_order_payment`
WHERE id_order >= @startID AND id_order <= @endID
AND id_order NOT IN (SELECT id_order FROM live.`ps_order_invoice_payment` WHERE id_order >= @startID AND id_order <= @endID)
ORDER BY backup_211014.`ps_order_payment`.`id_order_payment` ASC;

INSERT INTO live.`ps_order_return_detail` 
SELECT backup_211014.`ps_order_return_detail`.* FROM backup_211014.`ps_order_return_detail` 
INNER JOIN backup_211014.`ps_order_return` 
ON backup_211014.`ps_order_return_detail`.`id_order_return` = backup_211014.`ps_order_return`.`id_order_return`
WHERE id_order >= @startID AND id_order <= @endID
AND id_order NOT IN (SELECT id_order FROM live.`ps_order_return` WHERE id_order >= @startID AND id_order <= @endID)
ORDER BY backup_211014.`ps_order_return_detail`.`id_order_return` ASC;

INSERT INTO live.`ps_orders` 
SELECT * FROM backup_211014.`ps_orders` 
WHERE id_order >= @startID AND id_order <= @endID
AND id_order NOT IN (SELECT id_order FROM live.`ps_orders` WHERE id_order >= @startID AND id_order <= @endID)
ORDER BY id_order ASC;

INSERT INTO live.`ps_order_carrier` 
SELECT * FROM backup_211014.`ps_order_carrier` 
WHERE id_order >= @startID AND id_order <= @endID
AND id_order NOT IN (SELECT id_order FROM live.`ps_order_carrier` WHERE id_order >= @startID AND id_order <= @endID)
ORDER BY id_order ASC;

INSERT INTO live.`ps_order_cart_rule` 
SELECT * FROM backup_211014.`ps_order_cart_rule` 
WHERE id_order >= @startID AND id_order <= @endID
AND id_order NOT IN (SELECT id_order FROM live.`ps_order_cart_rule` WHERE id_order >= @startID AND id_order <= @endID)
ORDER BY id_order ASC;

INSERT INTO live.`ps_order_detail` 
SELECT * FROM backup_211014.`ps_order_detail` 
WHERE id_order >= @startID AND id_order <= @endID
AND id_order NOT IN (SELECT id_order FROM live.`ps_order_detail` WHERE id_order >= @startID AND id_order <= @endID)
ORDER BY id_order ASC;

INSERT INTO live.`ps_order_history` 
SELECT * FROM backup_211014.`ps_order_history` 
WHERE id_order >= @startID AND id_order <= @endID
AND id_order NOT IN (SELECT id_order FROM live.`ps_order_history` WHERE id_order >= @startID AND id_order <= @endID)
ORDER BY id_order ASC;

INSERT INTO live.`ps_order_invoice` 
SELECT * FROM backup_211014.`ps_order_invoice` 
WHERE id_order >= @startID AND id_order <= @endID
AND id_order NOT IN (SELECT id_order FROM live.`ps_order_invoice` WHERE id_order >= @startID AND id_order <= @endID)
ORDER BY id_order ASC;

INSERT INTO live.`ps_order_invoice_payment` 
SELECT * FROM backup_211014.`ps_order_invoice_payment` 
WHERE id_order >= @startID AND id_order <= @endID
AND id_order NOT IN (SELECT id_order FROM live.`ps_order_invoice_payment` WHERE id_order >= @startID AND id_order <= @endID)
ORDER BY id_order ASC;

INSERT INTO live.`ps_order_return` 
SELECT * FROM backup_211014.`ps_order_return` 
WHERE id_order >= @startID AND id_order <= @endID
AND id_order NOT IN (SELECT id_order FROM live.`ps_order_return` WHERE id_order >= @startID AND id_order <= @endID)
ORDER BY id_order ASC;

INSERT INTO live.`ps_order_slip` 
SELECT * FROM backup_211014.`ps_order_slip` 
WHERE id_order >= @startID AND id_order <= @endID
AND id_order NOT IN (SELECT id_order FROM live.`ps_order_slip` WHERE id_order >= @startID AND id_order <= @endID)
ORDER BY id_order ASC;

INSERT INTO live.`ps_paypal_order` 
SELECT * FROM backup_211014.`ps_paypal_order` 
WHERE id_order >= @startID AND id_order <= @endID
AND id_order NOT IN (SELECT id_order FROM live.`ps_paypal_order` WHERE id_order >= @startID AND id_order <= @endID)
ORDER BY id_order ASC;
  
COMMIT;

/* MIGRATE NEWS LETTER SUBS */
BEGIN;

SET @startID = 793;
SET @endID = 793;

INSERT INTO live.`ps_newsletter` 
SELECT * FROM backup_211014.`ps_newsletter` 
WHERE id >= @startID AND id <= @endID
AND id NOT IN (SELECT id FROM live.`ps_newsletter` WHERE id >= @startID AND id <= @endID)
ORDER BY id ASC;


COMMIT;

Edited October 21, 2014 by OllyL (see edit history)

[coniglio]

Hi OllyL, thanks for the script, but  my phpmyadmin answers

"Invalid Query: Column Count Doesn't Match Value Count At Row 1"

 

can you help-me?

[OllyL]

Hi Coniglio,

 

I would guess that the Prestashop version you are migrating to/from has a different table schema on one or more of the tables. I would recommend upgrading the old deployment of Prestashop (in a test environment) to match your target version and then try the query again.

Edited March 24, 2015 by OllyL (see edit history)

[coniglio]

Hi OllyL, thanks.

 

I saw that the data type is different, one is MyISAM and the other is InnoDB.

 

i think can be for this reason... right?

[OllyL]

No, I think you would get a different error if it was data types, you error very specifically says there is a column mismatch

[coniglio]

Ok i check and update you. Thanks a lot!

[coniglio]

Hi OllyL,

is true, the structure of the table is different. I have informed the table columns and now it's ok!

 

thanks you very much