Copy a MySQL table with phpMyAdmin

Posted in MySql -

Last week I looked at how to copy a table with MySQL using some SQL queries and then on Sunday a PHP script to automate the process. This post looks at how to copy a table with phpMyAdmin so you can easily do the same thing using a webpage GUI.

Log into phpMyAdmin, select the database and then the table that you wish to copy.

Click the "Operations" tab which appears in the tabs near the top of the page in the right frame. This tab is highlighted in the screenshot below with the red arrow pointing to it.

copy a table with phpmyadmin

The available operations appear underneath the tabs and there are various options. The one we are interested in with this post is the "Copy table to..." section which is highlighted with the red box.

You can either make a copy of the table to the current database, which is selected by default from the database drop down box, or copy it to another database for which you have the appropriate permissions.

After the drop down box is a dot ( . ) and then a textbox where you enter the name of the table to copy the structure and / or data into.

There are then three radio options:

  • structure only: this will create a copy of the table with the name you specify but won't copy any data over
  • structure and data: this will do the above and then copy all data across using INSERT INTO ... SELECT * FROM ...
  • data only: this only does the INSERT INTO ... SELECT * FROM ... query

You can also select whether to drop the table that you are copying to before creating the table and copying the data. This runs a DROP TABLE IF EXISTS query for the copy of the table, not the original. It has no effect if selected when doing a data only copy.

Another option is to copy the auto increment value. By default this is not done and the auto increment value will end up being whatever it would be after the INSERT INTO query is done. If checked then the CREATE TABLE syntax will include the auto increment value from the original table.

The final checkbox is to change to the copied table after the queries are done. If unchecked the resulting page will still be looking at the original table; if checked it will change to the copied database (if applicable) and table.



Related posts:


Comments