Our blog
MySQL Copy Table from One Database to Another
Sometime in MySQL you want to copy an entire table from one database to a separate database. One example of when you would want to do this is if you are making a backup of a table before you apply some kind of processing to that table. There is nothing worse than ruining a database table and losing valuable information. To make an instant backup is so easy that there really is no excuse not to make backups at important or risky stages.
Here is how to do it:
-
DROP TABLE IF EXISTS `backup_db.backup_table`;
-
CREATE TABLE `backup_db.backup_table` SELECT * FROM `live_db.live_table`;
This will delete your existing backup table completely, then will remake it copying all structural information and content from the live_table in the live_db.
Related Blogs
http://www.phpclasses.org/browse/package/4017.html
http://www.sematopia.com/?p=61
http://www.yinfor.com/blog/archives/2008/02/mysql_backup_and_recovery_meth.html
More Reading:
34 Comments
|
Just remember that this won't create the table using the same engine as the existing table and it won't recreate the primary keys or indexes. To do this you have to use: CREATE TABLE new_table_name LIKE old_table_name; then you populate it with the data from the old table with: INSERT INTO new_table_name SELECT * FROM old_table_name; If the copied old table has many keys then it may help to speed the INSERT if you turn off the keys using the following before the INSERT: ALTER TABLE new_table_name DISABLE KEYS; And then after the INSERT: ALTER TABLE new_table_name ENABLE KEYS; These two statements are not supported by all MySQL Engine types though. InnoDB being one of them in MySQL ver 5.x Cheers |
|
admin June 13th, 2008 |
Nice one Julz Yes if you want to do more than simply backup the table data and want to actually make a proper fully functional copy of the table then the above is definitely the best way to do so. |
|
tony October 2nd, 2008 |
hi, i want to update my table2 with only the new data that i have entered from table1. hopefully on a daily basis how wud i go about this? pls help tony |
|
tony October 3rd, 2008 |
any one? please |
|
Very useful tip, thanks to Julz and Admin |
|
Hi I made a little function for this query as per Julz advice PHP:
|
|
note thats using this db_query function PHP:
|
|
troy January 13th, 2009 |
Hey Guys, Im running the mysql 5.1.2 & myphpadmin 3.1 and this doesnt seem to work. it tries to copy the table to the same database. Any help please? |
|
can you tell us the exact sql you are pasting into the sql box on phpmyadmin? |
|
ZIA January 19th, 2009 |
Its so nice. Thaks for your conversations. But i need one more thing. how can i transfer a table data from one machine to another in MySQL?. |
|
the most usually way would be to dump the table data to an SQL file (export in phpMyAdmin) then save this file somewhere, upload it to the other machine and then import it. there are other ways involving getting the machines talkign to each other directly, but for most purposes the dump method is going to be the easiest and therefore best. |
|
tanvi January 30th, 2009 |
hai,i tried your code..but it is not working I tried with this in phpmyadmin: 1st db name :employees1 --->table name:hai My code is: but it shows error as in phpmyadmin.: MySQL said: Documentation Please help me as early as possible... |
|
tanvi January 31st, 2009 |
Please help me as soon as possible... |
|
are they MyISAM tables? |
|
Steve Childs February 2nd, 2009 |
Make sure that the user you are running the query as has sufficient rights for both the source and destination databases... Also make sure you're using the right quotes - you shouldn't need to use quotes at all in those queries you posted, try it without. The queries as posted here do work, I've just done them on two of my tables. The problem lies in your SQL server somewhere, my guess is one of the above reasons. |
|
James Campanella February 11th, 2010 |
To tanvi, I don't know if you've solved your problem yet or not, but it appears to me that the problem is in your back tick quotes. If you are specifying a database.table the proper backticks should be `database`.`table` for it to work in phpMyAdmin. It is not a bad idea to use quotes in the event you have a database name or field that ends up being a MySQL keyword or SQL command. Hope this helps. |
|
Ah yeah well spotted James |
|
MysQL vs. Oracle ? |
|
Where to find the best possible and secure database solutions for big databases ? any suggestions ? |
|
Hello, |
|
syed August 4th, 2010 |
How can i migrate a table from a database to another with different field names but field values are same. I also have extra columns to insert in new database table? Please help!!! Regards, |
|
naresh August 23rd, 2010 |
<?php $t_core_dir = dirname( __FILE__ ).DIRECTORY_SEPARATOR; require_once( $t_core_dir .'core.php' ); $db1 = "ams";//first schema name to be copied copy_db1_to_db2_table($t_copy_from,$t_copy_to); function copy_db1_to_db2_table($table_name, $backup_table_name){ db_query("CREATE TABLE $backup_table_name LIKE $table_name"); db_query("ALTER TABLE $backup_table_name DISABLE KEYS"); db_query("INSERT INTO $backup_table_name SELECT * FROM $table_name"); db_query("ALTER TABLE $backup_table_name ENABLE KEYS"); } ?> |
|
naresh August 23rd, 2010 |
this is for copying one DB schema to Another DB schema |
|
ijkui9jkj September 16th, 2010 |
# |
|
aom January 17th, 2011 |
thank you very much may I edit |
|
Vincent January 26th, 2011 |
how about if i want to copy the whole database in mysql? without using backup? just a query..help please... |
|
atul March 25th, 2011 |
this query doesnt work for my sql i tried with my database and table |
|
SM April 13th, 2011 |
I would like to know how to update a table after it has been copied from another table. I mean that i need to update the new table with the other's new inerted rows on a daily or monthly basis for example i.e. incrementally, does anyone know this can be done ?? |
|
jairaj June 9th, 2011 |
Hi, My database is on a server. It gets update every time and keep on filling the rows in my database. My friend's database is on different server. I want to update my friend's database as soon as my database gets any update. Please help me...... |
|
Andre July 6th, 2011 |
Hi, is there any way to copy the indexes as well?? Cheers |
|
newbie July 8th, 2011 |
Hi, I have to copy only one column from one mysql db table to another running on different server. They have given me a wsdl for updating in destination db. the source database is in local environment. Can anyone please tell me how do I retrieve the data from sql convert into a SOAP webservice as defined by the wsdl? I have to do this in php. And I'm newbie. Any help would be greatly appreciated. |
|
ano September 26th, 2011 |
the script will work if you are in the db other than the one you are altering- |
|
JD October 19th, 2011 |
these suggestions are overcomplicated. its pretty easy to copy the table create statement, run it in the new db and then run: INSERT INTO newDB.table that copies all the indices and other table characteristics. plus, you don't have to mess with drop table. i just did this for nine tables in less than 15 min. if you are asking, 'how can i copy the CREATE statement' you should get Sequel Pro or similarly awesome UI for your DB. |
|
Tendai Gomo November 28th, 2011 |
This is all useful stuff. Thanks so much. What if i want to select a particular row at the front-end of an application using PHP code. Then, by clicking a button inside the row, the data is immediately transferred to another database? |
RSS Feed
Julz
June 12th, 2008