(SQL Tutorials for Citizen Data Scientist)

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:

Step 2: Inserting Data into 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

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:

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

Disclaimer: The information and code presented within this recipe/tutorial is only for educational and coaching purposes for beginners and developers. Anyone can practice and apply the recipe/tutorial presented here, but the reader is taking full responsibility for his/her actions. The author (content curator) of this recipe (code / program) has made every effort to ensure the accuracy of the information was correct at time of publication. The author (content curator) does not assume and hereby disclaims any liability to any party for any loss, damage, or disruption caused by errors or omissions, whether such errors or omissions result from accident, negligence, or any other cause. The information presented here could also be found in public knowledge domains.