SQL Cloning A Table

In this lesson, you will learn how to create a duplicate copy of an existing database table using SQL.

How to Clone / Copy A Table In SQL

You might need to create a full or partial copy of a database table if you are going to test a complex query or just for auditing purposes.

To make the cloning process very simple, be sure that the original table and the copy table have identical structures (same field names and datatypes).

1- Creating The Copy Table

In general, you can create the copy table using the CREATE TABLE syntax that works on all kinds of databases as follows:

CREATE TABLE table_name (
    column_name_1 datatype,
    column_name_2 datatype,
    ....
); 

However, since we are using MySQL we can use its way to create a copy of the table structure.

CREATE TABLE new_table LIKE original_table;

2- Copying Records Syntax

Copying table records to another table can be done using the INSERT INTO and the SELECT statements together as follows.

INSERT INTO new_table
SELECT * FROM original_table
WHERE condition;

Preparing A Database

Since you will learn how to clone tables, you must have a database table with some records to practice cloning it. Execute the following query to create the database.

-- If there is already a database named "demo" delete it
DROP DATABASE IF EXISTS demo;

-- Create a new database called "demo"
CREATE DATABASE demo;

-- Select the "demo" database
USE demo;

-- Create a new table called "employees" inside the selected database
CREATE TABLE employees (
    id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    salary DECIMAL(7,2),
    birthday Date
);

-- Insert 5 records in the "employees" table
-- We add null in the id column because its value will be generated automatically by the database server
INSERT INTO employees VALUES (null, "Emily", "Johnson", 800, "1990-10-23");
INSERT INTO employees VALUES (null, "Benjamin", "Anderson", 750, "1992-05-08");
INSERT INTO employees VALUES (null, "Olivia", "Smith", 900, "1988-07-12");
INSERT INTO employees VALUES (null, "William", "Davis", 845, "1994-12-04");
INSERT INTO employees VALUES (null, "Sophia", "Brown", 1000, "1991-10-27");
    

This is the employees table that we created in the demo database.

Copy Table Structure Example

The following query creates a new table called "employees_copy" which has the same structure as the employees table.

CREATE TABLE employees_copy LIKE employees;
    

CREATE TABLE employees_copy (
    id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    salary DECIMAL(7,2),
    birthday Date
);
    

The employees_copy table that we have created has the same structure as the employees table.

Copy Table Data Example

The following query selects all the records from the employees table and then inserts them in the employees_copy table.

INSERT INTO employees_copy
SELECT * FROM employees;
    

If you select all records in the employees_copy you will notice that it becomes an exact copy of the employees table.

The following query will try to reinsert the same records again in the employees_copy table.

INSERT INTO employees_copy
SELECT * FROM employees;
    

The following query selects all non-unique values from the employees table and then inserts them in the employees_copy table.

The database server will automatically generate an id value for the newly added rows in the employees_copy table because its type is AUTO_INCREMENT.

INSERT INTO employees_copy(first_name, last_name, salary, birthday)
SELECT first_name, last_name, salary, birthday FROM employees;
    

If you select all records in the employees_copy you will notice that the records are added successfully and the database server generates values for the id field automatically for the new records.

Clone Table Exercise

1- Write a query that creates a new database called "training".
Note: you should recreate it if it already exists.

2- Write a query that creates a table called "customers" and check whether it exists or not. the table should contain the following fields:

• first_name VARCHAR(50)
• last_name VARCHAR(50)
• phone VARCHAR(20)

3- Write a query that inserts 5 rows in the "customers" table.

4- Write a query that creates a new copy of the "customers" table called "customers_copy".

5- Write a query that returns all records in the "customers_copy" table.

DROP DATABASE IF EXISTS training;

CREATE DATABASE training;

USE training;

CREATE TABLE IF NOT EXISTS customers (
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    phone VARCHAR(20)
);

INSERT INTO customers (first_name, last_name, phone) VALUES
('Jack', 'Reed', '10203040'),
('Emma', 'Clark', '55512388'),
('Luke', 'Lee', '32452012'),
('Jim', 'Wilson', '96112377'),
('Lily', 'Brooks', '36018090');

CREATE TABLE customers_copy LIKE customers;

INSERT INTO customers_copy
SELECT * FROM customers;

SELECT * FROM customers;