Search code examples
mysqldump

How to dump data from one table and insert to another

I have two databases. I want to dump data from one table in 1st database and insert to another table with an another name in 2nd database.
So I have DB1 that has tables tbl1 and tabl2, and DB2 that has tables tbl3 and tbl4. I know that tabl1 and tabl3 have the same structure. How to copy data from one to another by using mysqldump command?
I've tried to do this, but it's not work.

mysqldump --user root --password=password --no-create-info DB1 tbl1 > c:/dump.sql
mysql --user root --password=password DB2 tbl3 < c:/dump.sql
Solution

  • This is not going to work due to different table name

    if both database are sitting in the same server using the same daemon, you can directly 

    insert into DB2.tbl3 select * from DB1.tbl1;

    if tbl1 is not existing in DB2,
    pseudo code for this :

    # import as tbl1 from DB1 into tbl1 in DB2
mysqldump DB1 tbl1 | mysql DB2

# then rename tbl1 in DB2 to tbl3
mysql DB2 -N <<< "rename table tbl1 to tbl3"