MySQL: Clone/Duplicate Table (with or without data)

This article will show different ways to clone or duplicate a MySQL table. All the available methods are discussed. Choose the process which suits your need.

Using Command-Line

Method #1: Clone schema and data using CREATE TABLE … SELECT

This is the simplest way to clone/duplicate a database with schema and all data. It uses CREATE TABLE … SELECT statement to clone table schema and data. Check the documentation for CREATE TABLE … SELECT here: https://dev.mysql.com/doc/refman/5.6/en/create-table-select.html

CREATE TABLE new_table_name AS (SELECT * FROM existing_table_name);

Use this when you want all the data copied from the existing table to a new table.

Any condition can be used to in the SELECT query to ensure that only desired rows are copied into the new table.

Note: the AUTO_INCREMENT attribute and indexes are not copied to this new table. Also, some data type conversion can happen.

Method #2: Clone schema only using CREATE TABLE … LIKE

This process only clones the table schema. No data is copied. This uses CREATE TABLE … LIKE statement to create a new table from the existing table. Check the documentation for CREATE TABLE … LIKE here: https://dev.mysql.com/doc/refman/8.0/en/create-table-like.html

CREATE TABLE new_table_name LIKE existing_table_name;

Use this process if you don’t want any data from the existing table.

Note: the indexes are copied but the AUTO_INCREMENT attribute is not.

Method #3: Clone table using mysqldump

This process uses mysqldump to clone a table with or without data. Use this if you want to clone/duplicate the table from one server to another.

This is a 2-step process:

Step 1: Export table in SQL file

To export table schema with data use the following command.

mysqldump -u db_user_name -p db_password database_name table_name > backupfile.sql

Or to export table schema only (without data) use the following command, which includes a flag ‘-d’:

mysqldump -u db_user_name -p db_password  -d database_name table_name > backupfile.sql

Step 2: Import table from SQL file

mysqldump -u db_user_name -p db_password database_name < backupfile.sql

In this method, all table constraints will be copied. So the data types of columns, indexes, and AUTO_INCREMENT property will same as the source table.

Using GUI

MySQL Workbench

MySQL workbench does not provide any option to clone tables directly. Though it provides an option to copy the schema of create statement. To access that:

• Right-Click on the table name.
• Go to option “Copy to Clipboard“.
• Click on “Create Statement“.

That will copy the table’s create a schema to your clipboard.

phpMyAdmin

phpMyAdmin gives options to copy tables from one database to another database. For that:

• Go to the table list.
• Select any number of tables.

• Go to the bottom of the table list.
• From the dropdown select “Copy table“.
• A modal will appear with options. Choose options from there.

• Click on the “Continue” button.

HeidiSQL

HeidiSQL gives a direct option to clone a table. To clone a table in HeidiSQL follow the steps:

• Right-Click on the table name.
• Go to Option “Create new” > “Table copy“.

• A new window with an option will appear.
• Select the options and click “Ok“

SQLyog

SQLyog gives a direct option to clone a table. Use the following steps:

• Right-Click on the table name
• From the options go to “More Table Options” > “Duplicate Table Structure/Data“.

• A new window will appear.
• Choose the correct options and click on “Copy” button..