[thelist] MySQL, copy between databases

Jay Blanchard jay.blanchard at niicommunications.com
Wed Sep 17 06:32:35 CDT 2003

Is there any way to copy records between databases without exporting and
then importing?

In database db1, there is a table called db1_table1. In database db2,
is an identical table called db2_table1. I want to copy records from
db1_table1 to db2_table2.

INSERT INTO db2_table1 (put, each, column, except, auto-increment,
index, if, you, have, one)
SELECT get, each, column, except, auto-increment, index, if, you, have,
FROM db1_table1

The great thing about this is that if you have a table you wish to only
copy select columns with certain criteria

INSERT INTO db2_table1 (put, foo) // may have several columns
SELECT get, foo 
FROM db1_table1 // may have several columns
WHERE bar = 'make seven'
AND gark = 'up yours'
ORDER BY fooIndex

Ah, you say, but you didn't go between databases. True I say, but that
is as easy as aliasing, or you could use full references....

(one database is called fish, one is caled trout)

INSERT INTO trout.db2_table1(put, each, column)
SELECT fish.get, fish.each, fish.column
FROM fish.db1_table1

HTH! The reference at http://www.mysql.com is your friend.

More information about the thelist mailing list