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.
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:
Now, use the following statement to populate the empty table with data 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.
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
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:
The following command creates a simple copy of the employees table.