Mysql – Sqlyog: copy table from localhost to remote server without duplicate records

MySQL

Hi guys i want to copy record from table which is stored in localhost into remote server but when i tried to copy there is pop up like below:

enter image description here

If i checked Drop if exists in target it would delete all of data in table, but if unchecked it would duplicate several record,that's why i want to copy record from table in localhost into remote server without duplicate data or delete existing data, so just adding new data from localhost table and ignoring existing data. Is there any way to use such a feature like that in sqlyog ? if not how could i to do this task ?

Best Answer

You can copy your table first into a temporary table in your source database, copy that table to the target database, and merge the data there.

That is, in your source database: 

CREATE TABLE emails_temp AS
SELECT 
    * 
FROM 
    emails ;

... copy this table to the target database using the Copy Database functionality; and drop it from the original:

 DROP TABLE emails_temp ;

Once you have your temporal table at the destination, add the new data to the existing table, doing something like:

INSERT INTO emails
SELECT 
    * 
FROM 
    emails_temp
WHERE 
    primary_key NOT IN (SELECT primary_key FROM emails) ;

(Obviously, you have to substitute primary_key by your actual PRIMARY KEY).

Or you can use also INSERT IGNORE.

After you have transferred all the new rows, you can

DROP TABLE emails_temp ; -- At the destination database
Related Question