how to duplicate table in mysql

How To Duplicate Table in MySQL

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.

 

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!

mm

About Ubiq

Ubiq is a powerful dashboard & reporting platform. Build dashboards, charts & reports for your business in minutes. Try it for free!