Duplicating a MySQL table

28 February, 2014 by Tom Elliott

Here’s a straightforward SQL statement that allows one table to easily be duplicated in MySQL without affecting the existing table. The below SQL makes a copy of the existing table structure (without the data), preserving all field names and indexes.

CREATE TABLE new_table_name LIKE existing_table_name;

If you want to duplicate a table, whilst preserving all data then you can use the following SQL, with an additional ‘INSERT’ command appended to the SQL above:

CREATE TABLE new_table_name LIKE existing_table_name; 
INSERT new_table_name SELECT * FROM existing_table_name;

There are a number of reasons you might want to duplicate a table in MySQL. For example, if you want to make a copy of the table for testing or development purposes, without interfering with the live table.

Duplicating a MySQL table in PhpMyAdmin

If you’re using PHPMyAdmin, duplicating tables is really easy. Under the ‘SQL’ tab for the selected database, just copy and paste the SQL above substituting in the new and existing table names.

---

---