Mysql copy data from one database to another WHERE

Question

I am trying to copy data from one database into another. Essentially, I have 2 databases.

Customer (which has ID, Name, DOB, Address)
Full Customer (which has ID, PHOTO, SIGNATURE)(expanding to include Name, DOB, Address)

All customers are stored in the Customer database (10 million records), but only some are stored in the "Full Customer" database. (5 million records).

I want to copy all the Customer records into the Full Customer database where there is no record currently.

My pseudo code is below for programming, but i am wondering if i can do this directly with mysql

for customer in customers:
    if customer.ID not in (select ID from fullcustomer)
        insert customer into fullcustomer

Kleskowy · Accepted Answer · 2016-04-15 13:42:38Z

1

You can, and you should do it directly with MySQL.

Try this. I assumed tables are in the same database - if not, you should prefix the table names with database names. I also assumed missing columns in full_customer table are already there (you have put it there).

It basically means: "Insert into table full_customer records of users from table customer, that are not already there, using ID for comparison".

INSERT INTO full_customer (ID, Name, DOB, Address) (
   SELECT ID, Name, DOB, Address FROM customer c
   LEFT JOIN full_customer fc ON c.ID = fc.ID
   WHERE fc.ID IS NULL
)

What you should do is to do a sanity check beforehands, to check whether the provided SQL will select the customers you actually want to insert to the full_customer table:

SELECT ID, Name, DOB, Address FROM customer c
LEFT JOIN full_customer fc ON c.ID = fc.ID
WHERE fc.ID IS NULL

Make sure the ID column in both tables is indexed, otherwise it will take forever to execute these statements.

answered Apr 15, 2016 at 13:42

Kleskowy

2,6681 gold badge18 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Mark Over a year ago

this worked great. Thanks for the help. The join worked great - needed a lot of testing as you mentioned.

Collectives™ on Stack Overflow

Mysql copy data from one database to another WHERE

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related