How to Duplicate a Table in MySQL?

In this article, we'll look at ways to copy a table and its data into a new table in MySQL. For the examples in this article, we'll be using the following table:

+----+--------+------------+
| id |  name  | date_login |
+----+--------+------------+
|  1 |   foo  | NULL       |
|  2 |   bar  | 2020-12-24 |
|  3 |   baz  | 2021-03-02 |
+----+--------+------------+

CREATE TABLE user (
    id INT NOT NULL AUTO_INCREMENT,
    name VARCHAR(5),
    date_login DATE,
    PRIMARY KEY (id)
);

INSERT INTO user(name, date_login) VALUES
('foo', NULL), 
('bar', '2020-12-24'), 
('baz', '2021-03-02');

Using CREATE TABLE ... LIKE

Simply using the following would copy the table structure and keys from one table to another:

CREATE TABLE `user_copy` LIKE `user`;

Please note that while this would recreate the indexes from the original table, it would not copy any triggers, stored procedures, foreign key constraints, or DATA DIRECTORY or INDEX DIRECTORY table options.

Using CREATE TABLE ... LIKE on Temporary Tables:

If the original table is a TEMPORARY table, by default CREATE TABLE ... LIKE will not preserve TEMPORARY. To create a TEMPORARY destination table, use CREATE TEMPORARY TABLE ... LIKE.

Using CREATE TABLE ... SELECT

CREATE TABLE ... SELECT copies the table structure and data without copying over any indexes, stored procedures, triggers, and metadata of the source table. It has the following syntax:

CREATE TABLE `user_copy` AS SELECT * FROM `user`;

Creating Indexes With CREATE TABLE ... SELECT:

MySQL intentionally does not automatically create any indexes when using CREATE TABLE ... SELECT to make the statement as flexible as possible. If you want to have indexes in the created table, you should specify these before the SELECT statement, like so:

CREATE TABLE `user_copy` (UNIQUE (`name`)) SELECT * FROM `user`;

If you wish to automatically copy over indexes from the source table, you could instead consider using CREATE TABLE ... LIKE.

Appending Columns From One Table to Another:

By Default, CREATE TABLE ... SELECT appends columns from the SELECT statement to the right side of the table. So, for example, the following would create one column that is defined in the CREATE TABLE part and append the rest of the columns copied over from the SELECT statement:

CREATE TABLE `user_copy` (`test` INT) SELECT * FROM `user`;

This would result in the following:

+------+----+--------+------------+
| test | id |  name  | date_login |
|------+----+--------+------------+
| NULL |  1 |   foo  | NULL       |
| NULL |  2 |   bar  | 2020-12-24 |
| NULL |  3 |   baz  | 2021-03-02 |
+------+----+--------+------------+

Alternatively, we could populate the data for the new column, for example, like so:

CREATE TABLE `user_copy` (`test` INT) SELECT *, id+1 AS `test` FROM `user`;

This would output the following:

+----+--------+------------+------+
| id |  name  | date_login | test |
|----+--------+------------+------+
|  1 |   foo  | NULL       |   2  |
|  2 |   bar  | 2020-12-24 |   3  |
|  3 |   baz  | 2021-03-02 |   4  |
+----+--------+------------+------+

From the above output you can see that the test column appears at the end. This happens because in a table resulting from CREATE TABLE ... SELECT, columns named only in the CREATE TABLE part come first, and columns named in both parts or only in the SELECT part come after that.

Copy as New Column Names:

You can specify different column names in your new table like so:

CREATE TABLE `user_copy` SELECT `id` AS `new_id`, `name` AS `new_name` FROM `user`;

This would result in:

+--------+----------+
| new_id | new_name |
|--------+----------+
|    1   |    foo   |
|    2   |    bar   |
|    3   |    baz   |
+--------+----------+

Take Note:

When using CREATE TABLE ... SELECT, please remember:

• By specifying the same column name in the CREATE TABLE part, we can override the data type of the SELECT columns.
• If any errors occur while copying the data to the new table, it is automatically dropped and not created.
• To handle duplicate unique key values, you can use the IGNORE or REPLACE, preceeding the SELECT statement. IGNORE would discard duplicates on the unique key column, while REPLACE would enforce new rows to replace rows that have the same unique key value.
• Duplicate unique key values will result in an error, if neither IGNORE nor REPLACE is specified.

Using SHOW CREATE TABLE

You could get the CREATE query for the table you wish to copy like so:

SHOW CREATE TABLE `user`;

This would, for example, output:

CREATE TABLE user (
    id INT NOT NULL AUTO_INCREMENT,
    name VARCHAR(5),
    date_login DATE,
    PRIMARY KEY (id)
);

After that, you can simply change the name of the table (and indexes, if any) to the new table, like so:

CREATE TABLE user_copy (
    id INT NOT NULL AUTO_INCREMENT,
    name VARCHAR(5),
    date_login DATE,
    PRIMARY KEY (id)
);

This would create a new table with the same structure.

Copying Data From the Source Table Using INSERT INTO

The following INSERT statement would copy over data from the old table to the new one:

INSERT INTO `user_copy` SELECT * FROM `user`;

Checking Keys of the New Table

To verify if the keys were correctly copied over, you could use the following:

SHOW KEYS FROM `user_copy`;