SQL CLONE TABLE – Duplicate Structure or Structure + Data

Introduction – What is SQL CLONE TABLE?

Cloning a table in SQL means creating an exact copy of an existing table’s structure, optionally including its data. This is useful for backup, testing, staging, and archive purposes.

In this guide, you’ll learn:

• How to clone a table structure only
• How to clone structure with data
• Syntax variations across SQL platforms

1. Clone Table Structure Only

-- PostgreSQL / SQL Server / SQLite
CREATE TABLE new_table AS
SELECT * FROM old_table WHERE 1=0;

-- MySQL
CREATE TABLE new_table LIKE old_table;

Creates a new table with the same column definitions but no data.

2. Clone Table Structure + Data

CREATE TABLE new_table AS
SELECT * FROM old_table;

Copies both schema and data, excluding constraints and indexes (platform-dependent).

3. Use INSERT INTO for Existing Target

INSERT INTO existing_table
SELECT * FROM old_table;

Use when the structure already exists, and you want to copy just the data.

4. Does It Copy Indexes, Keys, Triggers?

5. Clone with Filters (Partial Copy)

CREATE TABLE top_customers AS
SELECT * FROM customers
WHERE total_spent > 1000;

Create table clone with filtered subset of original data.

Best Practices

Summary – Recap & Next Steps

Cloning a table is useful for testing, backup, analysis, and archiving. Choose the method based on whether you want to clone structure, data, or both.

Key Takeaways:

• CREATE TABLE AS clones structure + optionally data (no constraints)
• CREATE TABLE LIKE (MySQL) includes keys and indexes
• Use INSERT INTO SELECT to clone just the data into an existing table

Real-World Relevance:
Used in backups, staging environments, data snapshots, A/B tests, and analytics.

FAQ – SQL CLONE TABLE

How do I clone just the structure of a table?

Use CREATE TABLE new_table AS SELECT * FROM old_table WHERE 1=0;

Does CREATE TABLE AS copy constraints and indexes?

No. Only column names and data types.

What’s the difference between CREATE TABLE LIKE and AS SELECT?

LIKE copies constraints and indexes (MySQL); AS copies structure and optionally data.

Can I clone only some rows into a new table?

Yes. Use a WHERE clause to filter data when using AS SELECT.