How to Duplicate Table Structures & Data in MySQL

MySQL comes packed with cool features, making it super easy to manage your databases!

In this tutorial, we'll cover duplicating MySQL table structures and copying all data to a new table with just a few commands. This is great when you need to make copies or backups of tables in a pinch.

Copy a Table's Structure

Here is the MySQL command to clone an existing table's structure into a new table:

CREATE TABLE new_table AS SELECT * FROM original_table;

This command copies an existing table's structure into a new table, including the basic column definitions, NULL settings, and default values from the original table. This does not however copy other types of data including indexes or auto_increment definitions. Just make sure you double-check your work.

If we want to inherit all table definitions when creating a new table, then we can run this command instead:

CREATE TABLE new_table LIKE original_table;

This command will perform an exact copy of the original table into our newly created table.

Be careful when cloning larger tables, especially on production servers, as this can eat up a lot of resources and potentially stall your environment while the duplication is running.

Copy a Table's Data

Now, to copy the data over from the existing table to our newly created table, we can run the following MySQL command:

INSERT INTO new_table SELECT * FROM original_table;

Again, take caution when copying data from large tables as it can cause unwanted results by hogging resources in your environment.

Conclusion

As you can see, cloning MySQL table structures and data is extremely easy with these quick, single-line commands!