copy a table from one db to another

Posted on 2004-04-30
Medium Priority
Last Modified: 2011-08-18
Dear All,

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

Question by:ping1234
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 3
LVL 14

Expert Comment

ID: 10957872
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?
LVL 14

Expert Comment

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


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:


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

Accepted Solution

hendridm earned 400 total points
ID: 10969030
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
Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI


Author Comment

ID: 10974246
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??

LVL 14

Expert Comment

ID: 10974274
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 Comment

ID: 10975359
Thanks for both of your support!
LVL 14

Expert Comment

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

Author Comment

ID: 10975567
um...I just think hendridm's answer could help more directly..
LVL 14

Expert Comment

ID: 10975586
Your call.

Featured Post

Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This post contains step-by-step instructions for setting up alerting in Percona Monitoring and Management (PMM) using Grafana.
In this blog post, we’ll look at how ClickHouse performs in a general analytical workload using the star schema benchmark test.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question