This article will show you the BEST way to copy a table, with or without the data in it, in MySQL and MariaDB.
Copying the Table with All Data in MySql
The following code will copy a table including all data using the CREATE TABLE LIKE statement:
CREATE TABLE new_table_name LIKE database_name.old_table_name; INSERT new_table_name SELECT * FROM database_name.old_table_name;
There are other methods, some single line, but this is probably the best and simplest one.
Why did I choose this method? Because it copies the table indexes – including primary keys – and the AUTO_INCREMENT value so that when new data is inserted, it is inserted at the next available auto-increment rather than being inserted mid-table. Triggers are also copied. The new table is in the same state as the one it is being copied from.
The new table being created doesn’t have to be in the same database as the table being copied – in the example above, the new table will be created in the currently active database, and the database containing the table being copied can be specified.
Copying the Table Structure Only
To copy only the table structure, ignore the second line above and simply run:
CREATE TABLE new_table_name LIKE database_name.old_table_name;
This will create a new table with the same structure as the old one, but it will be completely empty of data.
As usual, never run untested queries on your production database — and always have an up-to-date backup of your data before running any queries.
