Copy data from database1 to database2

I want c开发者_Python百科opy data of table1 from databse1 to table2 database2. both tables table1 and table2 structures are identical

---

INSERT INTO database2.table2 SELECT FROM database1.table1 if they are on the same machine, otherwise you need to dump/load.

---

Assuming there's no data in db2.table2:

INSERT INTO db2.table2
SELECT * FROM db1.table1

You can use either DELETE or TRUNCATE prior to the statement above to clean out a table:

DELETE FROM db2.table2
TRUNCATE TABLE db2.table2

The difference is there's no control in TRUNCATE -- all data is removed, and there's no log file update in order to revert back to previous data so be careful.

This assumes the user has sufficient privileges in both databases.

---

Enter the source database, database1:

use database1;

Then write to which fields in the destination database you want to copy to, database2:

INSERT INTO database2.table1 (field1,field3,field9)
SELECT table2.field3,table2.field1,table2.field4
FROM table2;