How to Copy Table in MySQL: Duplicate Structure, Data, and Indexes Correctly

Copying tables in MySQL is a routine operation performed by DBAs, developers, and analysts dozens of times a day for various reasons and with various purposes. In this tutorial, we provide a detailed overview of the most common methods to copy a MySQL table structure and data.

How to copy a table in MySQL

MySQL doesn't have the copy table statement, which means you'll have to use sideways approaches to perform the operation. There are three popular ways to clone a table in MySQL.

1. CREATE TABLE ... AS SELECT statement to copy the source table column attributes and data.

CREATE TABLE new_table_name
FROM old_table_name;

2. CREATE TABLE ... LIKE statement to create an empty table based on the definition of the original table, including column attributes and indexes.

CREATE TABLE new_table_name LIKE old_table_name;

3. SHOW CREATE TABLE to generate a create table script for the original table.

Replicate table in MySQL

MуSQL copy table structure only

Let's now take a closer look at ways to create a table in MySQL by using SQL statements.

If you need to duplicate the table structure, but not its data, it is better to use the CREATE TABLE ... LIKE statement.

Suppose, we need to clone the structure of the customer table. The query to copy structure is as follows:

CREATE TABLE customer_new LIKE customer

In case you need to copy the table structure including primary keys, foreign keys and constraints, run the SHOW CREATE TABLE ... statement, then copy the script for the original table, change the name of the table and execute the script.

 How to copy structure, not data of a MySQL table

Copy table data to another table

To copy data from one table to another, use the INSERT INTO statement. Note, that you can specify the columns to be copied.

INSERT INTO target_table (`column1`, `column2`)
  FROM source_table;

This method works perfectly well if you have already copied the table structure like we did in the example above and now need to copy values.

In case you want to duplicate all rows from the source table, use the following syntax:

CREATE TABLE new_table_name LIKE old_table_name; 
INSERT new_table_name SELECT * FROM old_table_name;
Duplicate all rows from one table to another

Copy a table from one database to another

In MySQL, the easiest way to copy a table with its data between two databases is to use the CREATE TABLE AS statement, but note, that you need to provide the target database name as a table prefix.

SELECT * FROM old-database.old-table-name;

If you need to copy to another database schema only the table structure, use the CREATE TABLE LIKE statement, but again don't forget to specify the database names. Remember that by a schema in MySQL is typically meant a system schema. Simply put, a schema in MySQL corresponds to the concept of a database in SQL Server.

LIKE old-database.old-table-name;

However, by running the query above, you will create a clone of the source table with all the column attributes, indexes, and keys. To copy the table structure only, you can run use a LIMIT clause in the query to make MySQL left aside the data.

FROM from old-database.old-table-name LIMIT 0;
Clone a table from one db to another

Copy a table to a new table: Example

In MySQL, you don't have to copy the entire table, you can copy the specific columns only. For this, you can use the CREATE TABLE and the SELECT statement as shown below:

CREATE TABLE new_table_name 
SELECT column1, column2, column3 

Suppose, we want to create a new table film_copy which will consist of three columns: film_id, film_title, and film_description.

CREATE TABLE film_copy
FROM film f;
MySQL copy table to new table

The fastest way to copy a table in MySQL:
dbForge Studio for MySQL

dbForge Studio for MySQL delivers a quick and easy way to copy a table, even a large one, without coding—via the comprehensive and user-friendly graphic interface.

Step 1. Right-click the table you want to copy in Database Explorer and select Duplicate Object.
Step 2. In the dialog that opens, select the destination connection and database.
Step 3. Specify the name of the new table, and click OK.
Step 4. Select to copy the table data or structure only.
Step 5. Select to drop the existing destination objects or leave them intact.

Keep in mind that if you select Drop destination object, a DROP IF EXISTS operation is performed, followed by CREATE. If you are working with a table object, all its data will be irreversibly lost.

Still want to create table by writing SQL code?
dbForge Studio has a lot to offer in that case too. An advanced SQL Editor comes with sophisticated code completion, robust syntax check, instant code formatter, and dozens of other features invaluable for hand coding.

The fastest and the easiest way to create a table in MySQL is definitely by using dbForge Studio for MySQL!


In this article, we have demonstrated the most popular ways to create a duplicate table in MySQL: by different SQL statements and by using an all-in-one IDE for MySQL database development, management, and administration—dbForge Studio that allows you to perform the any database-related tasks in a GUI. Within just a few clicks, you can rename a MySQL table, perform MySQL database migration, duplicate a MySQL database, and much, much more.

dbForge Studio for MySQL

Unleash your database development potential

Availability in the editions of dbForge Studio for MySQL


Duplicate Object