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.
|
1 |
CREATE TABLE destination_table SELECT col_1,col_2, col_3.. from source_table WHERE condition |
In the syntax,
- 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_nameformat - column_name: If you want to populate the specific columns from the source table, you can specify the column names
- 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_nameformat - 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:
- 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
- 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.
|
1 |
Select * from movies; |
To copy the data from the film (source table) to the film_backup (destination) table.
|
1 |
create table movies_backup select * from movies; |
Once data is copied, run the SELECT statement to view the data.
|
1 |
Select * from movies_backup; |
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.
|
1 |
create table tbl_movies_Rating_NC17 select * from movies WHERE rating='NC-17'; |
Once data has been copied, run the SELECT statement on tbl_movies_rating_NC17.
|
1 |
select * 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:
|
1 |
Create database DEV_SakilaDB; |
To copy the data, we will run the following query:
|
1 |
create table `DEV_SakilaDB`.`tblactor` select * from `sakila`.`actor`; |
Once data has been copied, run the following SELECT statement.
|
1 |
select * from `DEV_SakilaDB`.`tblactor`; |
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.
- To create a table, use CREATE TABLE destination_table LIKE source_table
- 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.
|
1 |
SHOW CREATE TABLE actor; |
Following is the table structure of the actor table.
|
1 2 3 4 5 6 7 8 |
CREATE TABLE `actor` ( `actor_id` smallint unsigned NOT NULL AUTO_INCREMENT, `first_name` varchar(45) NOT NULL, `last_name` varchar(45) NOT NULL, `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (`actor_id`), KEY `idx_actor_last_name` (`last_name`) ) ENGINE=InnoDB AUTO_INCREMENT=201 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
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.
|
1 |
create table `tblActor_backup` select * from `actor`; |
Once data has been copied, run SHOW CREATE TABLE statement to view the structure of the tblActor_backup table.
|
1 |
SHOW CREATE TABLE tblActor_backup; |
Structure of tblActor_backup table is the following:
|
1 2 3 4 5 6 |
CREATE TABLE `tblactor_backup` ( `actor_id` smallint unsigned NOT NULL DEFAULT '0', `first_name` varchar(45) NOT NULL, `last_name` varchar(45) NOT NULL, `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
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.
|
1 |
create table `tblActor_backup` LIKE `actor`; |
Once data has been copied, run the SHOW CREATE TABLE to view the structure of tblActor_backup table.
|
1 |
SHOW CREATE TABLE `tblActor_backup`; |
|
1 2 3 4 5 6 7 8 |
CREATE TABLE `tblactor_backup` ( `actor_id` smallint unsigned NOT NULL AUTO_INCREMENT, `first_name` varchar(45) NOT NULL, `last_name` varchar(45) NOT NULL, `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (`actor_id`), KEY `idx_actor_last_name` (`last_name`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
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.
|
1 |
INSERT INTO `tblActor_backup` select * from `actor`; |
Run SELECT statement to view the data.
|
1 |
Select * from `tblActor_backup` |
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
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
- Different ways to identify and change compatibility levels in SQL Server - July 22, 2024
- Copy SQL Databases between Windows 10 and CentOS using SQL Server data tools - October 19, 2022
- Changing the location of FILESTREAM data files in SQL Database - October 14, 2022