Easiest way to copy a table from one database to another?

Question

If you have shell access you may use mysqldump to dump the content of database1.table1 and pipe it to mysql to database2. The problem here is that table1 is still table1.

mysqldump --user=user1 --password=password1 database1 table1 \
| mysql --user=user2 --password=password2 database2

Maybe you need to rename table1 to table2 with another query. On the other way you might use sed to change table1 to table2 between the to pipes.

mysqldump --user=user1 --password=password1 database1 table1 \
| sed -e 's/`table1`/`table2`/' \
| mysql --user=user2 --password=password2 database2

If table2 already exists, you might add the parameters to the first mysqldump which dont let create the table-creates.

mysqldump --no-create-info --no-create-db --user=user1 --password=password1 database1 table1 \
| sed -e 's/`table1`/`table2`/' \
| mysql --user=user2 --password=password2 database2

CREATE TABLE db1.table1 SELECT * FROM db2.table1

where db1 is the destination and db2 is the source

If you are using PHPMyAdmin, it could be really simple. Suppose you have following databases:

DB1 & DB2

DB1 have a table users which you like to copy to DB2

Under PHPMyAdmin, open DB1, then go to users table.

On this page, click on the "Operations" tab on the top right. Under Operations, look for section Copy table to (database.table):

& you are done!

MySql Workbench: Strongly Recommended

This will easily handle migration problems. You can migrate selected tables of selected databases between MySql and SqlServer. You should give it a try definitely.

I use Navicat for MySQL...

It makes all database manipulation easy !

You simply select both databases in Navicat and then use.

 INSERT INTO Database2.Table1 SELECT * from Database1.Table1

If your tables are on the same mysql server you can run the following

CREATE TABLE destination_db.my_table SELECT * FROM source_db.my_table;
ALTER TABLE destination_db.my_table ADD PRIMARY KEY (id); 
ALTER TABLE destination_db.my_table MODIFY COLUMN id INT AUTO_INCREMENT;