Solved

copy a table from one db to another

Posted on 2004-04-30
9
56,324 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
[X]
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
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
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 

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

The Ultimate Checklist to Optimize Your Website

Websites are getting bigger and complicated by the day. Video, images, custom fonts are all great for showcasing your product/service. But the price to pay in terms of reduced page load times and ultimately, decreased sales, can lead to some difficult decisions about what to cut.

Question has a verified solution.

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

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…
Introduction This article is intended for those who are new to PHP error handling (https://www.experts-exchange.com/articles/11769/And-by-the-way-I-am-New-to-PHP.html).  It addresses one of the most common problems that plague beginning PHP develop…
There's a multitude of different network monitoring solutions out there, and you're probably wondering what makes NetCrunch so special. It's completely agentless, but does let you create an agent, if you desire. It offers powerful scalability …
In this video we outline the Physical Segments view of NetCrunch network monitor. By following this brief how-to video, you will be able to learn how NetCrunch visualizes your network, how granular is the information collected, as well as where to f…

705 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