How To Use LOAD DATA LOCAL INFILE REPLACE

Question

I have a script that I am using to migrate data from one db to another. I have already done so with using regular mysql insert and update scripts. It just takes way too long.

In any case I have dumped all of the data that I want to update in the new db to a csv, I am just worried that if I run this script I am going to get duplicate entries. I dont have an "id" that matches up so I can't do a comparison on the REPLACE script listed below. I was wondering if anyone could give me a heads up on whether or not this script looks correct.

Currently I have rows of data in the new db, I just want to overwrite any fields in the new db with this new data in the csv's if they match. The "archived_id_number" field is the only field that I could match on, but it is not a Primary Key.

Can someone shoot me the following:

I want to replace any data in fields with data in the csv's if the archived_id_number on the csv matches what is in the new db. If there is no match, the I want to insert the data as a new row.

$sql = '
LOAD DATA LOCAL INFILE "'.$theFile.'" 
REPLACE INTO TABLE Product 
FIELDS TERMINATED BY "|" 
LINES TERMINATED BY "\\n"
(archived_id_number, sku, name, upc, account_id, shippingBox_id, unit_cost, supplier_id, description, productLength, productWidth, productHeight)
;';

Thanks for your help!!!

Although it's not a primary key, I assume there's a `UNIQUE` on `archived_id_number`? — Wrikken, Jan 07 '14 at 23:44
Then it should work as far as I can see without the original table build & a csv snippet. I'd run it on a test copy of the table first though... — Wrikken, Jan 07 '14 at 23:53
If in doubt, create a duplicate of the target table and then import into that duplicate. This way you can see what will happen when you import for real without risking real data. As stated, what you have written looks fine. — Simon at The Access Group, Jan 08 '14 at 01:01

score 2 · Answer 1 · edited May 23 '17 at 12:30

As an elaboration of my comment, while your query looks fine I advise testing this against a copy of your current table. This will allow you to confirm precisely what will happen without risking your existing data. You can achieve this by doing the following 3 queries (obviously chuck in a the php to execute $sql).

$sql = '
    CREATE TABLE `_test_Product` LIKE Product; 
';

$sql = '
    INSERT `_test_Product` SELECT * FROM Product;
';

$sql = '
    LOAD DATA LOCAL INFILE "'.$theFile.'" 
    REPLACE INTO TABLE `_test_Product`
    FIELDS TERMINATED BY "|" 
    LINES TERMINATED BY "\\n"
    (
        archived_id_number, 
        sku, name, upc, account_id, shippingBox_id, unit_cost, 
        supplier_id, description, productLength, productWidth, productHeight
    );
';

(more info available in Duplicating a MySQL table, indexes and data )

How To Use LOAD DATA LOCAL INFILE REPLACE

1 Answers1