Copy table from one database to another mysql workbench

This article explains the MySQL copy table process. In this article, you will learn how we can copy data from one table to another table. These tables could be in the same database or different databases. The MySQL copy table process can copy a specific dataset or all data from the source table to the destination table. We can use the MySQL copy table process to replicate the issues that occurred on production servers, which helps developers rectify the issues quickly.

To copy the data between the tables, we use the combination of CREATE TABLE and SELECT statement. If you want to copy the specific dataset from the table, we can filter the records by adding a WHERE clause. The syntax to copy the table and data is following.

In the syntax,

1. destination_table: Specify the destination table name. We can create a table using the column structure of the source table. If the destination table is in a different database, you can use db_name.schema_name.table_name format
2. column_name: If you want to populate the specific columns from the source table, you can specify the column names
3. Source_table: Specify the source table name. If the source table is in a different database, then you can use db_name.schema_name.table_name format
4. Condition: Specify the condition to populate to filter the records. If you want to copy the specific dataset from the source table, you can use the WHERE clause

When you copy the data from the source table to destination tables, MySQL performs the following tasks:

1. Create a new table with the name specified in the destination_table parameter. The destination table structure contains the columns that the SELECT statement has returned
2. Copy the data from the source table to the destination table

Now, let us see some examples. For demonstration, we are going to use the Sakila database. We are using MySQL workbench.

Example 1: Copy the entire table with data

Suppose we want to copy the data from the movies table to the movies_backup table. To view the data, run below the SELECT statement.

To copy the data from the film (source table) to the film_backup (destination) table.

Once data is copied, run the SELECT statement to view the data.

As you can see, the database has been copied to the movies_backup table.

Example 2: Copy partial data using WHERE clause

Suppose you want to create a table that has a list of movies whose rating is NC-17. In this example, the source table is movies, and the destination table is tbl_movies_Rating_NC17. To filter the data, we are using the WHERE clause on the rating column.

Once data has been copied, run the SELECT statement on tbl_movies_rating_NC17.

As you can see, data has been copied.

Example 3: Copy tables between the databases

In this example, we will see how we can copy the data from the source table to the destination table in another database. To demonstrate, I have created a database named DEV_SakilaDB, and we will copy the data from the actor table of the sakila database to the tblActor table of the DEV_SakilaDB database.

The following query creates a new database named DEV_SakilaDB:

To copy the data, we will run the following query:

Once data has been copied, run the following SELECT statement.

As you can see, the data has been copied to the Dev_SakilaDB.tblActor table.

Example 4: MySQL copy table process to clone the table

When we use MySQL copy table process using CREATE TABLE.. SELECT statement does not create indexes, constraints, primary keys, foreign keys associated with the source table. If you want to clone the source table, we can use the following method.

1. To create a table, use CREATE TABLE destination_table LIKE source_table
2. To copy the data, use INSERT INTO destination_table SELECT * FROM source_table

Let us understand with a simple example. We are using the actor table. To view the table structure, we can use SHOW CREATE TABLE [tbl_name] statement. Run the following query.

Following is the table structure of the actor table.

Now, let us run the CREATE TABLE .. SELECT statement to copy the data. The source table is actor and destination table are tblActor_backup. Query is following.

Once data has been copied, run SHOW CREATE TABLE statement to view the structure of the tblActor_backup table.

Structure of tblActor_backup table is the following:

As you can see in the above script, the primary key of tblActor table is missing. Now, let us run the CREATE TABLE.. LIKE .. statement to create a table.

Once data has been copied, run the SHOW CREATE TABLE to view the structure of tblActor_backup table.

As you can see, the CREATE TABLE script has a primary key.

Now run INSERT INTO.. SELECT statement to insert the data from source table to destination table.

Run SELECT statement to view the data.

Query Output

As you can see, the data has been copied to the tblActor_backup table.

Summary

In this article, we learned about the MySQL copy table process. To copy the, We can use CREATE TABLE.. SELECT statement. We also learned CREATE TABLE USING statement to create a clone of the source table and use the INSERT INTO SELECT statement to copy the data. We learned these methods using various examples.

Table of contents

• Author
• Recent Posts

Nisarg Upadhyay is a SQL Server Database Administrator and Microsoft certified professional who has more than 8 years of experience with SQL Server administration and 2 years with Oracle 10g database administration.

He has expertise in database design, performance tuning, backup and recovery, HA and DR setup, database migrations and upgrades. He has completed the B.Tech from Ganpat University. He can be reached on

How do I copy a table from one database to another in SQL Workbench?

How do I copy a table from one MySQL database to another?

How do I copy a table data from one database to another database?

How do I copy a database from one MySQL workbench to another server?