SQL Cloning Tables

In this tutorial you will learn how to create a duplicate copy of an existing table.

Cloning or Copying a Table

There may be a situation when you just want to create an exact copy or clone of an existing table to test or perform something without affecting the original table.

The following section describes how to do this in few easy steps.

Step 1: Creating an Empty Table

First use the following statement to create an empty table based on the definition of original table. It also includes the column attributes and indexes defined in the original table:

CREATE TABLE new_table LIKE original_table;

Step 2: Inserting Data into Table

Now, use the following statement to populate the empty table with data from original table:

INSERT INTO new_table SELECT * FROM original_table;

Let's make a clone of the table using the MySQL command-line tool.

Consider we've an employees table in our database that has the following records:

+--------+--------------+------------+--------+---------+
| emp_id | emp_name     | hire_date  | salary | dept_id |
+--------+--------------+------------+--------+---------+
|      1 | Ethan Hunt   | 2001-05-01 |   5000 |       4 |
|      2 | Tony Montana | 2002-07-15 |   6500 |       1 |
|      3 | Sarah Connor | 2005-10-18 |   8000 |       5 |
|      4 | Rick Deckard | 2007-01-03 |   7200 |       3 |
|      5 | Martin Blank | 2008-06-24 |   5600 |    NULL |
+--------+--------------+------------+--------+---------+

Execute the following SQL statement, it will create an empty table employees_clone based on the definition of existing employees database table.

mysql> CREATE TABLE employees_clone LIKE employees;

Now, execute another SQL statement which inserts all the records from employees table into employees_clone table. After executing this statement you'll get the employees_clone table which is an exact copy or duplicate of the employees table

mysql> INSERT INTO employees_clone SELECT * FROM employees;

Simple Cloning

However, if you just want to create a table from another table without taking into account any column attributes and indexes you can use the simple one line statement:

CREATE TABLE new_table SELECT * FROM original_table;

The following command creates a simple copy of the employees table.

mysql> CREATE TABLE employees_dummy SELECT * FROM employees;

Tip: Use the CREATE TABLE ... SELECT syntax to quickly create a simple copy of any table that only includes the structure and data of the source table.

Previous Page Next Page