Copy structure and all entries from one table to another (or how to create copy of existing table)

Posted by nikola, With 0 Comments, Category: Databases, MySQL, Tags: ,

Creating copy of existing MySQL table is useful when you want to prevent loose of existing data from it, or when you want to archive existing table.

There are several ways to to this.

One of the ways is to create new table like existing and to add same data with next two queries:

CREATE TABLE new_table LIKE old_table;
INSERT INTO new_table SELECT * FROM old_table;

First will create only a table with same structure, second will copy a data.

And don't forget to copy table keys:

CREATE TABLE new_table LIKE old_table;
ALTER TABLE new_table DISABLE KEYS
INSERT INTO new_table SELECT * FROM old_table;
ALTER TABLE new_table ENABLE KEYS;

mysql-logo-wallpaper