Solved

copy a table from one db to another

Posted on 2004-04-30
9
56,219 Views
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?


Thanks
0
Comment
Question by:ping1234
  • 5
  • 3
9 Comments
 
LVL 14

Expert Comment

by:cracky
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?
0
 
LVL 14

Expert Comment

by:cracky
ID: 10957938
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
 
LVL 8

Accepted Solution

by:
hendridm earned 100 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
0
 

Author Comment

by:ping1234
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??

0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 14

Expert Comment

by:cracky
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.
0
 

Author Comment

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

Expert Comment

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

Author Comment

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

Expert Comment

by:cracky
ID: 10975586
Your call.
0

Featured Post

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Join & Write a Comment

Foreword In the years since this article was written, numerous hacking attacks have targeted password-protected web sites.  The storage of client passwords has become a subject of much discussion, some of it useful and some of it misguided.  Of cou…
As a database administrator, you may need to audit your table(s) to determine whether the data types are optimal for your real-world data needs.  This Article is intended to be a resource for such a task. Preface The other day, I was involved …
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

757 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now