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

Copying MySQL tables is a routine operation that can be performed by DBAs, developers, and analysts dozens of times a day for various purposes. Here are some of the cases when you need to have a quick way to copy a table at hand.

Read the following guide to find out:

You must back up your table before modifying it

One of the most common situations is when you need to introduce certain changes to the data stored in your table. Naturally, a backup/copy made beforehand is a perfect precaution.

You want to test the results of a complex query

Another case comes when you need to test a complex SQL query on your table. You make a copy and conduct your testing on it. If something goes wrong, you can always go back.

You are migrating to a new environment

You might need to move a table structure and/or its data to a new database or environment—for instance, when you are moving to a new MySQL/MariaDB instance.

Your table goes through different development stages

Finally, there can be a case when your table needs to go all the way from development to testing and production—and you simply copy it to each subsequent environment.

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 duplicate a table in MySQL.

1. The CREATE TABLE ... AS SELECT statement copies the source table column attributes and data, but without indexes and constraints. 

CREATE TABLE copy_table
AS
SELECT 
  * 
FROM source_table;

2. The CREATE TABLE ... LIKE statement creates 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 & databases. 

CREATE TABLE copy_table LIKE source_table;

If you want to create a table with the same 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. The SHOW CREATE TABLE statement generates a CREATE TABLE script for the original table. 

SHOW CREATE TABLE copy_actor;
Replicate table in MySQL
Add data

Copy MySQL table structure only

If you need to duplicate a 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 the 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 a structure of a MySQL table without its data

Copy MySQL table data to another table

To copy data to another table, 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 previous example.

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 MySQL 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; however, 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 a table structure to another database schema, use the CREATE TABLE LIKE statement. But again, don't forget to specify the database names. Remember that a schema in MySQL typically means a system schema. In other words, 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 use a LIMIT clause in the query to make MySQL leave the data aside. 

CREATE TABLE copy_database.copy_table 
SELECT * 
FROM source_database.source_table 
LIMIT 0;
Clone a table from one database to another

An example of copying
a MySQL table

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 ... SELECT statement as shown below: 

CREATE TABLE copy_table
SELECT 
column1, 
column2, 
column3 
FROM source_table;

Suppose, we want to create a new table called film_copy that consists 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;
Copy a MySQL table to a 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 intuitive user interface.

1. Right-click the table you want to copy in Database Explorer and select Duplicate Object.
2. In the dialog that opens, select the destination connection and database.
3. Specify the name of the new table and click OK.
4. Select to copy the table data or structure only.
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 will be performed and followed by CREATE. If you are working with a table object, all of its data will be irreversibly lost.

Still want to create tables by writing SQL queries?
dbForge Studio has a lot to offer in that case, too. An advanced SQL Editor comes with context-aware code completion, syntax check, code formatting, and dozens of other features that will double your coding speed.

A comparison of different methods to copy MySQL tables

Now let's make a brief recap of the main methods to make a MySQL table copy. Besides SQL statements and the Duplicate Object feature, the recap will include mysqldump, a well-known command-line utility that performs logical backups of MySQL databases.

Method What it does Disadvantages
CREATE TABLE ... AS SELECT Copies the column attributes and data of the source table Does not copy indexes and constraints (if you need them, that is)
CREATE TABLE ... LIKE Creates an empty table based on the definition of the original table, including column attributes and indexes None
INSERT INTO ... SELECT Works best for copying data to another table; can be used together with CREATE TABLE ... LIKE None
mysqldump Works from the command line and creates a set of SQL statements that can be executed to reproduce the original database object definitions and table data Has certain performance and scalability considerations
Duplicate Object (dbForge Studio for MySQL) Provides an intuitive GUI to copy MySQL tables in the fastest possible way, without writing any queries None

Performance optimization

Note that the copying of large tables with massive amounts of data may take time and, even more importantly, impact performance. That's why we would like to take note of a few things that may help you fine-tune it. These include the use of MySQL indexes, partitioning, and the InnoDB buffer pool optimization.

First, you can create indexes on one or more columns. Indexes act like pointers to rows, allowing your query to quickly determine what rows match the condition in your WHERE clauses, and retrieve other column values for those rows.

Next, you can apply partitioning, the process of splitting up a table into several units known as partitions, which are stored separately. When you try to access certain data with a query, the engine will look it up in the specified partition, which will reduce the query execution time and thus be good for performance.

Finally, the InnoDB storage engine maintains an area called the buffer pool for caching data and indexes in memory. Knowing how it works and using it to keep frequently accessed data in memory is a vital aspect of MySQL tuning.

Alternative ways of copying data in dbForge Studio

Are there any other methods to safely copy your data (and even entire databases) in dbForge Studio for MySQL? Yes! There are at least three ways at your disposal, and you can learn more about them from a dedicated article on our blog.

  • Schema & Data Compare - sync source schemas/data to a target database
  • Create Scripts Folder - generate scripts that can reconstruct a database
  • Copy Database - duplicate an entire database with all of its data

Conclusion

In this article, we have demonstrated the main ways to duplicate a table in MySQL, including different SQL statements and an all-in-one IDE for the development, management, and administration of MySQL and MariaDB databases—dbForge Studio. With its help, you can rename MySQL tables, perform MySQL database migration, duplicate entire MySQL databases, and much, much more.

A few words from our users

"...I love being able to completely copy a database from one server to another with a few clicks, which makes it super quick to keep my dev environment in sync with the live database..."
Scott M.,
CTO
"...It proves really simple to work with the DB, making copy/back up/restore positively effortless. Furthermore, the possibility to compare not only data but also schemas is outstanding..."
Agustin A.,
Software Engineer
"...I struggled for years to find an excellent MySQL management tool, and most of those, both free and paid options, had mostly horrible UIs or severely lacked features. dbForge just does everything right."
Akos B.,
Director of Backend Engineering

dbForge Studio for MySQL

Level up your database development and management

Download free trial

Availability in the editions of dbForge Studio for MySQL

Feature

Enterprise
Professional
Standard
Express
Duplicate Object
Yes
Yes
Yes
Yes

What else can dbForge Studio for MySQL do?

DB Project
Create projects
Code debugger
Debug SQL scripts
Code editor
Edit SQL code
Backup
Back up and restore databases
DB migration
Migrate databases
Export Import
Import and export data
Debugging
Debug stored procedures
Pivot table
Create pivot tables
Copy DB
Copy databases
Copy DB
Compare schemas
All features of dbForge Studio for MySQL

Frequently Asked Questions

How can I use dbForge Studio to copy a table in MySQL?

dbForge Studio offers multiple features that will help you copy MySQL/MariaDB tables or entire databases, both with and without data.

  1. Quick execution of corresponding statements in SQL Editor.
  2. Duplicate Object - a feature that can create a copy of a table in a target environment.
  3. Schema & Data Compare - a feature that allows synchronizing source schemas and data to target ones with a few clicks.
  4. Create Scripts Folder - a feature that creates a collection of scripts that can be used to reconstruct a database along with all of its data.
  5. Copy Database - a feature that creates a copy of a database.
Can I use dbForge Studio to write and run a query that will make a copy of the required table?

Yes. Simply open the Studio's SQL Editor and start writing your query. The Studio's context-aware code completion will help you do it much faster. Moreover, when writing queries, you can use other handy capabilities of the Studio, which include instant syntax validation, code formatting, and refactoring. To top it off, you have a library of predefined and custom code snippets.

What are the different ways to copy a MySQL table with and without data?

If you need to copy a table WITH data, you can use the following ways:

  1. You can use the Studio's integrated tool Schema Compare to synchronize the structure of a source table to a target table. Next, you can use its twin counterpart Data Compare to synchronize data in the same way. Both tools are highly flexible and quick, with intuitive wizards to make your experience smooth.
  2. Alternatively, you can use another feature called Copy Database, which helps copy entire databases, both with and without data. To copy a database with data, make sure the Include Data checkbox is selected.
  3. If you need to import data to a table from a file, you can use the Studio's data import feature. Similarly, it's fast, wizard-aided, and supports the following file formats: TXT, XLS, XLSX, MDB, XML, JSON, CSV, ODBC, DBF, and Google Sheets.
  4. In case you want to simply copy data to another table, you can write and run a query with an INSERT INTO statement, having specified the required data in a SELECT ... FROM clause.
  5. Finally, you can use the Studio's Duplicate Object feature, which reduces the whole operation to just a few clicks. One of those clicks defines whether you want to include data—and if you do, simply select the Copy data checkbox.

If you need to copy a table WITHOUT data, you can use the following ways:

  1. You can write a CREATE TABLE ... LIKE query and run it against the required database.
  2. The Studio's integrated tool Schema Compare will help you synchronize the structure of a source table to a target table.
  3. You can use the previously mentioned Copy Database feature. This time, to copy a database without data, make sure the Include Data checkbox is cleared.
  4. Finally, you can use the same Duplicate Object feature. To copy a table without data, clear the Copy data checkbox.
What is the fastest way to create a MySQL table copy that includes indexes and constraints?

The fastest way is to run a query with the CREATE TABLE ... LIKE statement, which creates an empty table based on the definition of your source table, including indexes and constraints.

Can I copy a MySQL table with foreign keys and relationships preserved?

You can do it using the CREATE TABLE ... LIKE statement. Alternatively, you can use the Duplicate Object feature of dbForge Studio. It automatically generates a script that creates a copy of your table. Although the script does not include foreign keys by default, you can easily edit it and add the required commands to create foreign keys—or you can simply run these commands after the duplication.

Does dbForge Studio provide an option to automate MySQL table copying with scripts?

The Duplicate Object feature of dbForge Studio automatically generates a T-SQL script that creates a copy of the required object (e.g., a table). You can directly edit, save, and then run this script either from the Studio's SQL Editor or from the command line.

We use cookies to provide you with a better experience on the Devart website. You can read more about our use of cookies in our Cookies Policy.
Click OK to continue browsing the Devart site. Be aware you can disable cookies at any time.