Sometimes you may need to copy table in MySQL. There are many different ways to copy table in MySQL. Here are the steps to copy table in MySQL. You can use them to copy table to another table, copy table from one database to another, copy table structure, copy table with data, copy table structure with index, or copy table from one server to another.
How To Copy Table in MySQL
Here’s how to copy table in MySQL. There are different use cases to copy table in MySQL. We will look at each of them in detail.
MySQL Copy data from one table to a new table
Typically, you need to copy table in MySQL to a new table. In this case, you need to use CREATE TABLE and SELECT statement as shown below
CREATE TABLE new_table
SELECT column, column2, column3
FROM
existing_table;
In the above query, you need to specify names of existing table along with the columns that you want to copy, and also new table name. MySQL will populate your new table with the result of your SELECT statement.
Let’s say you have an existing table product_list
mysql> select * from product_list; +------------+--------------------+-------+ | product_id | product_name | price | +------------+--------------------+-------+ | 1 | iPhone 11 | 400 | | 2 | Samsung Galaxy A50 | 250 | +------------+--------------------+-------+
Here’s the SQL query to copy product_list table into new table called new_product_list
mysql> create table new_product_list
select *
from product_list;
mysql> select * from new_product_list;
+------------+--------------------+-------+
| product_id | product_name | price |
+------------+--------------------+-------+
| 1 | iPhone 11 | 400 |
| 2 | Samsung Galaxy A50 | 250 |
+------------+--------------------+-------+
Please note that the new table will be created only if it doesn’t exist. Otherwise, you will get an error. To avoid getting an error when you duplicate table in MySQL, you can use IF NOT EXIST clause in CREATE TABLE statement, as shown below.
mysql> create table if not exist new_product_list
select *
from product_list;
Bonus Read : How to Add NOT NULL constraint in MySQL
If you want to copy only partial data from one table to new table, that is some rows but not all, you can use WHERE condition in your SELECT statement as shown
CREATE TABLE new_table
SELECT column1, column2, column3
FROM
existing_table
WHERE
conditions;
Here’s the SQL query to copy partial data from product_list table into new table called new_products2
mysql> create table new_products2
select *
from product_list
where product_id=1;
mysql> select * from new_products2;
+------------+--------------+-------+
| product_id | product_name | price |
+------------+--------------+-------+
| 1 | iPhone 11 | 400 |
+------------+--------------+-------+
Bonus Read : How to Add Default Constraint in MySQL
MySQL Copy Table Structure to New Table
The above SQL queries only copy table and data. They will not copy table objects such as primary key, indexes, constraints, triggers. To copy table structure and data, you need to use CREATE TABLE statement with LIKE keyword, as shown below
CREATE TABLE IF NOT EXISTS new_table LIKE existing_table;
Let’s say you have an orders table with primary key (id)
mysql> describe orders; +--------------+--------------+------+-----+-------------------+----------------+ | Field | Type | Null | Key | Default | Extra | +--------------+--------------+------+-----+-------------------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | product_name | varchar(255) | NO | | NULL | | | order_date | date | YES | | NULL | | | price | int(11) | NO | | NULL | | | description | text | YES | | NULL | | | created_at | timestamp | NO | | CURRENT_TIMESTAMP | | +--------------+--------------+------+-----+-------------------+----------------+
and you want to copy structure of orders to new_orders table. Here’s the SQL query to copy table structure to new table.
mysql> create table new_orders like orders; mysql> describe new_orders; +--------------+--------------+------+-----+-------------------+----------------+ | Field | Type | Null | Key | Default | Extra | +--------------+--------------+------+-----+-------------------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | product_name | varchar(255) | NO | | NULL | | | order_date | date | YES | | NULL | | | price | int(11) | NO | | NULL | | | description | text | YES | | NULL | | | created_at | timestamp | NO | | CURRENT_TIMESTAMP | | +--------------+--------------+------+-----+-------------------+----------------+
As you can see, the new_orders table also has a primary key field id. The above statement will only copy table structure but not data. You need to separately copy data, as show in next section.
Bonus Read : MySQL Select Top N Rows Per Group
MySQL Copy Data from one table to existing table
The above statement will create new table with structure of old table, but it will be an empty table. Then you need to use INSERT statement to copy data from old table to new table.
INSERT new_table SELECT * FROM existing_table;
Here’s the SQL query to copy data from orders to existing table new_orders
mysql> insert new_orders
select * from orders;
mysql> select * from orders;
+----+--------------+------------+-------+-------------+---------------------+
| id | product_name | order_date | price | description | created_at |
+----+--------------+------------+-------+-------------+---------------------+
| 1 | A | 2020-07-01 | 150 | New product | 2020-06-01 00:00:00 |
| 2 | B | 2020-07-01 | 235 | Old product | 2020-06-15 00:00:00 |
+----+--------------+------------+-------+-------------+---------------------+
Bonus Read : MySQL Copy Database
MySQL Copy Table from One database to Another
If you want to duplicate table in MySQL from database to another, then just include the database names in your table names while using the above SQL queries, as shown below in bold
CREATE TABLE destination_db.new_table LIKE source_db.existing_table; INSERT destination_db.new_table SELECT * FROM source_db.existing_table;
The first SQL statement above will duplicate table structure in MySQL from source database (e.g source_db) to another (e.g destination_db). The second statement will copy data from one table to another.
Replace source_db and destination_db in above queries with source and destination databases respectively.
Hopefully, this article will help you copy table in MySQL. Please be careful when you copy table in MySQL, with large number of records, as it can take a lot of time & resources.
Ubiq makes it easy to visualize data in minutes, and monitor in real-time dashboards. Try it Today!
