[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 56418
  • Last Modified:

copy a table from one db to another

Dear All,

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


Thanks
0
ping1234
Asked:
ping1234
  • 5
  • 3
1 Solution
 
crackyCommented:
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?
0
 
crackyCommented:
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
0
 
hendridmCommented:
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
0
Restore individual SQL databases with ease

Veeam Explorer for Microsoft SQL Server delivers an easy-to-use, wizard-driven interface for restoring your databases from a backup. No expert SQL background required. Web interface provides a complete view of all available SQL databases to simplify the recovery of lost database

 
ping1234Author 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??

0
 
crackyCommented:
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.
0
 
ping1234Author Commented:
Thanks for both of your support!
0
 
crackyCommented:
ping. If we both helped you, why didn't you split the points?
0
 
ping1234Author Commented:
um...I just think hendridm's answer could help more directly..
0
 
crackyCommented:
Your call.
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 5
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now