How to dump data from one table and insert to another

Question

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 1

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"

Solution 2

I am using in a linux shell command line

mysqldump --user=username --password=xxxx dbname | mysql --host=remotehost.com --user=username --password=xxxx -C dname 

this transfers it from the local host to a remote host, the whole database.

Solution 3

IF you want to also copy the contents of the table you can do:

CREATE TABLE `new_table_name` LIKE `old_table_name`;
INSERT INTO `new_table_name` SELECT * FROM `old_table_name`;

If you have to copy table from one database to another database then use following

 CREATE TABLE `db1.new_table_name` LIKE `db2.old_table_name`;
 INSERT INTO `db1.new_table_name` SELECT * FROM `db2.old_table_name`;

It works for me as dumping single table and importing was throwing syntax error with MariaDB