How to Copy a Table in MySQL

This post covers how to copy a table in MySQL.

How to create a duplicate table structure in MySQL?

To copy the structure of an existing table, including its column definitions and indexes, but excluding the data, you can use the CREATE TABLE ... LIKE statement. This command clones the structure of the specified table into a new table.

CREATE TABLE new_table LIKE original_table;

How to copy data into the new table in MySQL

Once you have the new table, you can fill it with data from the original table using the INSERT INTO ... SELECT syntax. This command fetches data from the original table and inserts it into the new one.

INSERT INTO new_table SELECT * FROM original_table;

Example

Let's say you have a table named employees and you wish to create a duplicate named employees_backup. First, create the structure of the backup table:

CREATE TABLE employees_backup LIKE employees;

Next, populate the employees_backup table with the data from the employees table:

INSERT INTO employees_backup SELECT * FROM employees;

This ensures you have an exact copy of the employees table, with all its rows, columns, and indexes intact. But remember that this doesn’t replicate foreign key constraints or triggers. You’d need to add those manually to the new table.

Stuff to keep in mind

• Indexes and constraints: While the CREATE TABLE ... LIKE command replicates indexes, it doesn’t copy foreign key constraints. You should manually add these constraints to the new table if necessary.
• Data Consistency: To maintain data consistency, avoid modifying the original table while copying, particularly for large or production tables.
• Performance: Be mindful of the resource and time required to copy large tables. It's usually a good idea to perform such operations during periods of low database usage.