CODEWITHSUNDEEP
sqlmysqldata-migration
GSto
GSto

Reputation: 42350

Select & Insert across multiple databases with MySQL

I have 2 identical tables in 2 different databases that reside on the same server. What would be the best way to copy data from table to another?

Upvotes: 17

Views: 24906

Answers (2)

Danny Beckett
Danny Beckett

Reputation: 20806

Just to elaborate slightly on OMG Ponies' answer, you can use any WHERE clause, like so:

INSERT INTO db1.tablename
SELECT *
FROM db2.tablename src
WHERE src.lastlogin >= '2013-10-31 07:00:00'

Upvotes: 3

OMG Ponies
OMG Ponies

Reputation: 332661

Use:

INSERT INTO db1.table1
SELECT *
  FROM db2.table2 t2
 WHERE NOT EXISTS(SELECT NULL
                    FROM db1.table1 t1
                   WHERE t1.col = t2.col)

The exists is simplified, but you left out if there's a primary key/auto_increment to worry about/etc.

Upvotes: 30

Related Questions