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.
As you can see, cloning MySQL table structures and data is extremely easy with these quick, single-line commands!
There are no comments yet. Start the conversation!