Cara menggunakan mysql copy table

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.

Table of Contents Show

Example 1: Copy the entire table with data
Example 2: Copy partial data using WHERE clause
Example 3: Copy tables between the databases
Example 4: MySQL copy table process to clone the table
Table of contents
How do you clone a table?
How do you clone a table in database?
How do I copy a table from one schema to another in MySQL?
How do I copy a table to another table in MySQL workbench?

Table of Contents

Example 1: Copy the entire table with data
Example 2: Copy partial data using WHERE clause
Example 3: Copy tables between the databases
Example 4: MySQL copy table process to clone the table
Table of contents
How do you clone a table?
How do you clone a table in database?
How do I copy a table from one schema to another in MySQL?
How do I copy a table to another table in MySQL workbench?

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.

CREATETABLEdestination_tableSELECTcol_1,col_2,col_3..fromsource_tableWHEREcondition

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_name format
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_name format
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.

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

createtablemovies_backupselect*frommovies;

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

Select*frommovies_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.

createtabletbl_movies_Rating_NC17select*frommoviesWHERErating='NC-17';

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

select*fromtbl_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:

CreatedatabaseDEV_SakilaDB;

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

createtable`DEV_SakilaDB`.`tblactor`select*from`sakila`.`actor`;

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

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.

Following is the table structure of the actor table.

CREATETABLE`actor`(

`actor_id`smallintunsignedNOTNULLAUTO_INCREMENT,

`first_name` varchar(45)NOTNULL,

`last_name`varchar(45)NOTNULL,

`last_update` timestampNOTNULLDEFAULTCURRENT_TIMESTAMPONUPDATECURRENT_TIMESTAMP,

PRIMARYKEY(`actor_id`),

KEY `idx_actor_last_name`(`last_name`)

)ENGINE=InnoDBAUTO_INCREMENT=201DEFAULTCHARSET=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.

createtable`tblActor_backup`select*from`actor`;

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

SHOWCREATETABLEtblActor_backup;

Structure of tblActor_backup table is the following:

CREATETABLE`tblactor_backup`(

`actor_id`smallintunsignedNOTNULLDEFAULT'0',

`first_name` varchar(45)NOTNULL,

`last_name`varchar(45)NOTNULL,

`last_update` timestampNOTNULLDEFAULTCURRENT_TIMESTAMPONUPDATECURRENT_TIMESTAMP

)ENGINE=InnoDBDEFAULTCHARSET=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.

createtable`tblActor_backup`LIKE`actor`;

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

SHOWCREATETABLE`tblActor_backup`;

CREATETABLE`tblactor_backup`(

`actor_id`smallintunsignedNOTNULLAUTO_INCREMENT,

`first_name` varchar(45)NOTNULL,

`last_name`varchar(45)NOTNULL,

`last_update` timestampNOTNULLDEFAULTCURRENT_TIMESTAMPONUPDATECURRENT_TIMESTAMP,

PRIMARYKEY(`actor_id`),

KEY `idx_actor_last_name`(`last_name`)

)ENGINE=InnoDBDEFAULTCHARSET=utf8mb4COLLATE=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.

INSERTINTO`tblActor_backup`select*from`actor`;

Run SELECT statement to view the data.

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.

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

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 you clone a table?

Example.

Step 1 − Get the complete structure about the table..

Step 2 − Rename this table and create another table..

Step 3 − After executing step 2, you will clone a table in your database. If you want to copy data from an old table, then you can do it by using the INSERT INTO... SELECT statement..

How do you clone a table in database?

In Object Explorer right-click the table you want to copy and select Design. Select the columns in the existing table and, from the Edit menu, select Copy. Switch back to the new table and select the first row. From the Edit menu, select Paste.

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

Right-click on the database name then select "Tasks" > "Export data..." from the object explorer. 4. Provide authentication and select the source from which you want to copy the data; click "Next".