copy a table from one db to another

ping1234
ping1234 used Ask the Experts™
on
Dear All,

Can I ask the most proper way to copy a table (table structure + data) from one database to another?


Thanks
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Commented:
Couldn't be easier:

As of MySQL 3.23, you can create a table by selecting from another like so:

CREATE TABLE new_tbl SELECT * FROM orig_tbl;

If you are copying across databases, you need to use database.table syntax and make sure your mysql user has the appropriate permissions on both databases and tables.

CREATE TABLE new_database.new_tbl SELECT * FROM old_database.orig_tbl;

Does that solve your problem?

Commented:
There are a couple of things to consider when doing this. From the MySQL manual:

http://dev.mysql.com/doc/mysql/en/CREATE_TABLE.html

Indexes are not carried over to the new table, and some conversion of column types might occur. For example, the AUTO_INCREMENT attribute is not preserved, and VARCHAR columns can become CHAR columns.

When creating a table with CREATE ... SELECT, make sure to alias any function calls or expressions in the query. If you do not, the CREATE statement might fail or result in undesirable column names.

-----

You can get around this if you are happy to perform the copy with more than one query. You can use SHOW CREATE TABLE tbl_name syntax:

http://dev.mysql.com/doc/mysql/en/SHOW_CREATE_TABLE.html

Copy the output text, then run the exact same query with your destination database selected for use and you should get a more accurate result.

To move the data, you will then need to:

INSERT INTO new_database.new_tbl SELECT * FROM old_database.old_tbl
Commented:
You can also use command line utilities to do it.

For an entire database:
> mysqldump --all database_name > dump.txt
And then on your new database server (if it's different):
> mysqladmin create database_name
> mysql database_name < dump.txt

For a single table only:
> mysqldump --all database_name table_name > dump.txt
And then on your new database server (if it's different):
> mysql database_name < dump.txt
Rowby Goren Makes an Impact on Screen and Online

Learn about longtime user Rowby Goren and his great contributions to the site. We explore his method for posing questions that are likely to yield a solution, and take a look at how his career transformed from a Hollywood writer to a website entrepreneur.

Author

Commented:
thanks for the reply.

As the mysql environment is provided by a web hosting company, and I have to copy the table from userA a_db1 to userB b_db1 ...so this kind of table copy across db (and user) is still valid??

Commented:
No, you would need to dump the table first, then copy it in to the new one when logged in as another user. Could your hosting company not give one user permissions on both databases?

If you intend to do something like this often, I would recommend using phpMyAdmin www.phpmyadmin.net to administer your db. It allows you to dump structure and data from the source database really easily and then run the query on the destination database.

hendridm showed a really quick way to do this from the command line if that is an easier solution for you.

Author

Commented:
Thanks for both of your support!

Commented:
ping. If we both helped you, why didn't you split the points?

Author

Commented:
um...I just think hendridm's answer could help more directly..

Commented:
Your call.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial