How to Duplicate a Table in MySQL
You can duplicate or "clone" a table's contents by executing a CREATE TABLE ... AS SELECT
statement:
CREATE TABLE new_table AS SELECT * FROM original_table;
Please be careful when using this to clone big tables. This can take a lot of time and server resources.
Note also that new_table
inherits ONLY the basic column definitions, null settings and default values of the original_table
. It does not inherit indexes and auto_increment definitions.
To inherit all table definitions, use the CREATE TABLE... LIKE
syntax:
CREATE TABLE new_table LIKE original_table;
This makes the structure of new_table
exactly like that of original_table
, but DOES NOT copy the data. To copy the data, you'll need INSERT ... SELECT
:
INSERT INTO new_table SELECT * FROM original_table;
Again, be careful when doing this to big tables.
Previous
How to Truncate a TableFinally, a unified workspace for your SQL development
Get more done, together, with PopSQL and MySQL