Edmonds Commerce Blog

15 Responses to “MySQL Copy Table from One Database to Another”

1. Julz Says:
   June 12th, 2008 at 11:42 pm

   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
   Julz

2. admin Says:
   June 13th, 2008 at 8:47 am

   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.

3. tony Says:
   October 2nd, 2008 at 2:31 pm

   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

4. tony Says:
   October 3rd, 2008 at 7:57 am

   any one? please

5. rigo Says:
   October 12th, 2008 at 7:56 pm

   Very useful tip, thanks to Julz and Admin

6. admin Says:
   November 20th, 2008 at 12:04 pm

   Hi

   I made a little function for this query as per Julz advice

   PLAIN TEXT
   PHP:

   1. function backup_table($table_name, $backup_table_name){
   2.     db_query("CREATE TABLE $backup_table_name LIKE $table_name");
   3.     db_query("ALTER TABLE $backup_table_name DISABLE KEYS");
   4.     db_query("INSERT INTO $backup_table_name SELECT * FROM $table_name");
   5.     db_query("ALTER TABLE $backup_table_name ENABLE KEYS");
   6. }

7. admin Says:
   November 20th, 2008 at 12:05 pm

   note thats using this db_query function

   PLAIN TEXT
   PHP:

   1. function db_query($query){
   2.     $output = mysql_query($query) or die('<h1 style="color: red;">MySQL Error:</h1>Please copy and paste between the lines and email to YOUREMAIL@ADDRESS.COM<br><br>------------------------------------------------------------------------------------<h3>Query:</h3><pre>' . htmlentities($query) . '</pre><h3>MySQL Error:</h3>' . mysql_error() . '<br><br>------------------------------------------------------------------------------------');
   3.     return $output;
   4. }

8. troy Says:
   January 13th, 2009 at 2:52 pm

   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?

9. admin Says:
   January 13th, 2009 at 3:38 pm

   can you tell us the exact sql you are pasting into the sql box on phpmyadmin?

10. ZIA Says:
    January 19th, 2009 at 7:33 am

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

11. admin Says:
    January 19th, 2009 at 9:33 am

    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.

12. tanvi Says:
    January 30th, 2009 at 1:16 pm

    hai,i tried your code..but it is not working

    I tried with this in phpmyadmin:

    1st db name :employees1 --->table name:hai
    2nd db name:employees --->table name:hai

    My code is:
    DROP TABLE IF EXISTS `employees1.hai`;
    CREATE TABLE `employees1.hai` SELECT * FROM `employees.hai`;

    but it shows error as in phpmyadmin.:

    MySQL said: Documentation
    #1103 - Incorrect table name 'employees1.hai' ..

    Please help me as early as possible...

13. tanvi Says:
    January 31st, 2009 at 5:47 am

    Please help me as soon as possible...
    Its very urgent..

14. admin Says:
    January 31st, 2009 at 10:54 am

    are they MyISAM tables?

15. Steve Childs Says:
    February 2nd, 2009 at 12:47 am

    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.

Leave a Reply