SQL - Clone Tables

SQL - Cloning Tables

There are situations where you need to create a copy of a table, either for backup purposes, testing, or development. SQL provides several ways to clone a table, ranging from copying just the structure to copying both the structure and the data.

Method 1: Copying Structure and Data (CREATE TABLE AS)

The CREATE TABLE ... AS SELECT statement is a quick and common way to create a new table based on the result-set of a SELECT query. This method copies the column definitions, data types, and all the data from the source.

Important Note: This method typically does not copy constraints (like PRIMARY KEY, FOREIGN KEY) or indexes from the original table. You will need to add these manually afterwards.

Syntax

CREATE TABLE new_table_name AS
SELECT * FROM original_table_name;

Example

To create a complete backup copy of the Customers table, named Customers_Backup:

CREATE TABLE Customers_Backup AS
SELECT * FROM Customers;

After this command, Customers_Backup will exist with the same columns and all the same data as Customers, but it will lack the original primary key and other constraints.

Method 2: Copying Structure Only

Sometimes you only need an empty copy of a table with the same structure.

Approach A: LIKE clause (MySQL, PostgreSQL)

Some RDBMSs provide a LIKE clause in CREATE TABLE that copies the full structure, including columns, data types, and often constraints and indexes.

-- Creates an empty table 'NewCustomers' with the exact same structure as 'Customers'
CREATE TABLE NewCustomers (LIKE Customers INCLUDING ALL);

Approach B: CREATE TABLE AS ... WITH NO DATA (Standard)

A standard way to copy just the structure is to use CREATE TABLE AS with a WHERE clause that is always false. This creates the table based on the SELECT statement’s structure but retrieves no rows to insert.

CREATE TABLE NewCustomers AS
SELECT * FROM Customers WHERE 1 = 0; -- This condition is never true

Like the first method, this approach typically does not copy constraints or indexes.

The Complete Cloning Process

For a true, full clone including constraints, indexes, and data, a multi-step process is often required:

1. 1. Get the DDL of the original table: Use your database client to get the full CREATE TABLE statement for the original table. In MySQL, you can use SHOW CREATE TABLE original_table;. In PostgreSQL, you can use pg_dump -s -t original_table.
2. 2. Modify and run the DDL: Take the output from step 1, change the table name to your new clone’s name, and execute the script. This creates an exact structural replica, including all constraints and indexes.
3. 3. Copy the data: Use an INSERT INTO ... SELECT statement to populate the new table with data from the original.

-- Step 2 (Conceptual - assuming you have the DDL from Step 1)
CREATE TABLE Customers_Clone (
    -- ... full definition from original table ...
);

-- Step 3: Copy the data
INSERT INTO Customers_Clone
SELECT * FROM Customers;