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.

Read the guide to find out:

Ways to copy MySQL table definition in MySQL
SQL examples to copy a table in MySQL
How to copy table data and structure to another database
How to duplicate a MySQL table including indexes and data
How to insert table data into an already existing table
The fastest way to copy a table—by using the MySQL GUI tool

How to duplicate 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, but without indexes and constraints.

CREATE TABLE copy_table
AS
SELECT 
  * 
FROM source_table;

2. CREATE TABLE ... LIKE statement to create an empty table based on the definition of the original table, including column attributes and indexes. By the way, if you need to obtain indexes, refer to How to Show Indexes in MySQL Tables & Database.

CREATE TABLE copy_table LIKE source_table;

If you want to create a table with the structure, including indexes, and add data to it, you can run this query:

CREATE TABLE copy_actor LIKE actor;

INSERT INTO copy_actor
  SELECT
    *
  FROM actor;

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

SHOW CREATE TABLE copy_actor;

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 copy_customer 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 copy_table (
    column1, column2
)
  SELECT
    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 copy_table 
LIKE source_table; 
INSERT copy_table 
SELECT * FROM source_table;

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.

CREATE TABLE copy_database.copy_table
AS
SELECT * FROM source_database.source_table;

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.

CREATE TABLE copy_database.copy_table
LIKE source_database.source_table;

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.

CREATE TABLE copy_database.copy_table 
SELECT * 
FROM source_database.source_table 
LIMIT 0;

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 copy_table
SELECT 
column1, 
column2, 
column3 
FROM source_table;

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 copy_film
SELECT
  f.film_id,
  f.title,
  f.description
FROM film f;

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!

Conclusion

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.

Availability in the editions of dbForge Studio for MySQL

Feature

Enterprise

Professional

Standard

Express

Duplicate Object

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

How to duplicate a table
in MySQL

MуSQL copy table
structure only

Copy table data to
another table

Copy a table from one database to another

Copy a table to a new
table: Example

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

Conclusion

dbForge Studio for MySQL

Unleash your database development potential

Availability in the editions of dbForge Studio for MySQL

Feature

What else can dbForge Studio for MySQL do?

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

How to duplicate a table in MySQL

MуSQL copy table structure only

Copy table data to another table

Copy a table from one database to another

Copy a table to a new table: Example

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

Conclusion

dbForge Studio for MySQL

Unleash your database development potential

Availability in the editions of dbForge Studio for MySQL

Feature

What else can dbForge Studio for MySQL do?

How to duplicate a table
in MySQL

MуSQL copy table
structure only

Copy table data to
another table

Copy a table to a new
table: Example

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