Transfering MySQL Records from Database to Database

Question

I have two databases:

Transactions and Customer List

I want to populate the customer list database with the customer information that is in the transactions database. I do not wish to transfer ALL of the information from the transactions database because the Customer List database has a different structure.

How can I transfer these records? How can I skip over records that are already entered into the customer list database (there are more than one transaction per customer). The primary difference between customers is their ID number.

Any Ideas? Thanks!

Answer 1

It's probably best to write a script in the programming language of your choosing and run that script (Python,PHP,JAVA, anything).

You will then have granular programmatic control over WHAT gets transferred from the old database in addition to WHERE it gets transferred to in the new database.

This script would essentially be a loop that checks if the record you plan to enter from the old database exists in the new table. If not, enter it. If so, do not enter it.

You can test the script out first on dummy databases until you get it right (make sure you make a backup of your current databases).

Answer 2

if they are on the same server, you can simply use an INSERT...SELECT query across the two database schemas

INSERT INTO customerdb.customertbl (colA,colB)
SELECT foo,bar FROM transactiondb.customertbl WHERE criteria=whatever;