Nisarg Upadhyay
View data from a movies_backup table

Learn MySQL: MySQL Copy table

September 7, 2021 by

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.

View data from a movies table

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

Insert data in movies_backup table

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

View data from a movies_backup table

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.

Populate the data with NC-17 rating

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

View data from 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:

MySQL copy table process to copy data between databases

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

View data of tblActor table

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.

mysql copy table process to insert data in tblActor_backup table

Run SELECT statement to view the data.

Query Output

view data of tblActor_backup table

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

Learn MySQL: Querying data from MySQL server using the SELECT statement
Learn MySQL: What is pagination
Learn MySQL: Sorting and Filtering data in a table
Learn MySQL: Add data in tables using the INSERT statement
Learn MySQL: Create and drop temp tables
Learn MySQL: Delete and Update Statements
Learn MySQL: The Basics of MySQL Stored Procedures
Learn MySQL: The Basics of MySQL Views
Learn MySQL: An overview of MySQL Binary Logs
Learn MySQL: An overview of the mysqlbinlog utility
Learn MySQL: Run multiple instances of MySQL Server on Windows 10
Learn MySQL: MySQL String Functions
Learn MySQL: Control Flow functions
Learn MySQL: Install MySQL server 8.0.19 using a noinstall Zip archive
Learn MySQL: MySQL Copy table

Nisarg Upadhyay
Latest posts by Nisarg Upadhyay (see all)
Development, MySQL

About Nisarg Upadhyay

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 nisargupadhyay87@outlook.com

187 Views