Select & Insert across multiple databases with MySQL

Question

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?

Why do you have redundant data like that in the first place? — NullUserException, Aug 03 '10 at 18:42
@NullUserException I know that was an old comment, but it's useful for when wanting to extract subsets of data into a separate database for testing/debugging. — Danny Beckett, Oct 31 '13 at 09:12

score 30 · Accepted Answer · answered Aug 03 '10 at 18:42

30

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.

answered Aug 03 '10 at 18:42

OMG Ponies

325,700
82
523
502

5

what if db1 and db2 are on different ip addresses? – Shekhar Joshi Jul 24 '15 at 08:44

Danny Beckett · Answer 2 · 2014-09-18T23:18:08.240

3

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'

edited Sep 18 '14 at 23:18

answered Oct 31 '13 at 09:20

Danny Beckett

20,529
24
107
134

Select & Insert across multiple databases with MySQL

2 Answers2

Linked