📍 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?

Feature	Copied with CREATE TABLE AS	Copied with CREATE TABLE LIKE
Columns	✅	✅
Data	✅ (optional)	❌
Constraints	❌	✅ (MySQL only)
Indexes	❌	✅ (MySQL only)
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

✅ Recommended	❌ Avoid This
Use LIKE for index+constraint cloning	Assuming CREATE AS copies everything
Add indexes manually if required	Overwriting production tables by accident
Use clear names (_backup, _copy)	Copying system or temporary tables

---

📌 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.

---

Share Now :