[thelist] MySQL, copy between databases
Jay Blanchard
jay.blanchard at niicommunications.com
Wed Sep 17 06:32:35 CDT 2003
[snip]
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,
there
is an identical table called db2_table1. I want to copy records from
table
db1_table1 to db2_table2.
[/snip]
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,
one
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
LIMIT 12
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