SQL Copy Table

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

Clone or copy the table

In some cases, you may only want to create an exact copy or clone of an existing table to test or perform certain operations without affecting the original table.

The next section will explain how to perform this operation with several simple steps.

Step1：Create an empty table

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

CREATE TABLE new_table LIKE original_table;

Step2：Insert data into the table

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

INSERT INTO new_table SELECT　*　FROM original_table;

Let's use the MySQL command-line tool to clone the table.

Consider usemployeesThere is a table in the database that contains 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, which will create a table based on the existingemployeesThe definition of a database table creates an empty tableemployees_clone.

　employees_clone　　employees;

Now, execute another SQL statement that inserts all records from the employee table into the employees_clone table. After executing this statement, you will obtain the employee_clone table, which is an exact copy or duplicate of the employee table.

　employees_clone　　*　　employees;

Simple Clone

However, if you only want to create a table from another table without considering any column properties and indexes, you can use a simple one-line statement:

CREATE TABLE new_table SELECT　*　FROM original_table;

The following command createsemployeessimple copy of the table.

　employees_dummy　　*　　employees;