Last updated on September 11th, 2024 at 06:00 am
Sometimes you may need to copy data from one table to another in MySQL or copy tables in MySQL. Here’s how to duplicate table in MySQL. You can use it to clone table in MySQL or copy table structure, or even copy table from one database to another.
How To Duplicate Table in MySQL
Here are the steps to duplicate table in MySQL. There are different use cases to copy data from one table to another. We will look at each of them in detail.
MySQL Copy data from one table to a new table
If you need to duplicate table in MySQL to a new table, you need to use CREATE TABLE and SELECT statement as shown below
CREATE TABLE new_table
SELECT column, column2, column3
FROM
existing_table;
Let’s say you have an existing table products
mysql> select * from products; +------------+--------------------+-------+ | product_id | product_name | price | +------------+--------------------+-------+ | 1 | iPhone 11 | 400 | | 2 | Samsung Galaxy A50 | 250 | +------------+--------------------+-------+
Here’s the SQL query to copy products table into new table called new_products
mysql> create table new_products
select *
from products;
mysql> select * from new_products;
+------------+--------------------+-------+
| product_id | product_name | price |
+------------+--------------------+-------+
| 1 | iPhone 11 | 400 |
| 2 | Samsung Galaxy A50 | 250 |
+------------+--------------------+-------+
It is important to 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 in CREATE TABLE statement, as shown below.
mysql> create table if not exist new_products
select *
from products;
Bonus Read : MySQL Stored Procedure With Parameters
To copy only partial data from one table to new table, 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 products table into new table called new_products2
mysql> create table new_products2
select *
from products
where product_id=1;
mysql> select * from new_products2;
+------------+--------------+-------+
| product_id | product_name | price |
+------------+--------------+-------+
| 1 | iPhone 11 | 400 |
+------------+--------------+-------+
Bonus Read : How to Create Composite Primary Key in MySQL
MySQL Copy Table Structure to New Table
The above 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
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 | | +--------------+--------------+------+-----+-------------------+----------------+
Bonus Read : How to Truncate Table in MySQL
MySQL Copy Data from one table to existing table
The above statement will create new table with structure of old 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 DROP VIEW
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, 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 statement 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.
You can also use MySQLDump utility to duplicate table structure and/or data.
Hopefully, this article will help you duplicate table in MySQL. Please be careful when you duplicate 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!
Sreeram Sreenivasan is the Founder of Ubiq. He has helped many Fortune 500 companies in the areas of BI & software development.