SQL Cloning Tables

Previous Page Next Page

Cloning or Copying a Table

There are situations where you might need to create an exact copy or clone of an existing table for testing or other purposes without affecting the original table.

The following section explains how to do this in a few simple steps.

Step 1: Creating an Empty Table

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

CREATE TABLE new_table LIKE original_table;

Step 2: Inserting Data into the Table

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

INSERT INTO new_table SELECT * FROM original_table;

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

Imagine we have an employees table in our 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 |
+--------+--------------+------------+--------+---------+

Run the following SQL statement to create an empty table named employees_clone based on the structure of the existing employees table:

mysql> CREATE TABLE employees_clone LIKE employees;

Next, execute another SQL statement to insert all records from the employees table into the employees_clone table. After executing this statement, you will have an exact copy or duplicate of the employees table in the employees_clone table.

mysql> INSERT INTO employees_clone SELECT * FROM employees;

Simple Cloning

If you only want to create a table from another table without considering any column attributes and indexes, you can use the following simple one-line statement:

CREATE TABLE new_table SELECT * FROM original_table;

The following command creates a basic copy of the employees table:

mysql> CREATE TABLE employees_dummy SELECT * FROM employees;

Tip: Use the CREATE TABLE ... SELECT syntax to swiftly create a basic copy of any table, including only the structure and data from the source table.

Previous Page Next Page