How to Copy or Clone a Table in SQL

Posted by Kyle Hankinson October 24, 2024

---

Cloning a table is useful for backups, testing, and schema experiments. Every database can do it, but what gets copied varies.

MySQL

Structure + Data

CREATE TABLE users_copy AS SELECT * FROM users;

Warning: This copies data and column definitions but NOT indexes, primary keys, auto-increment, or foreign keys.

Structure Only (No Data)

CREATE TABLE users_copy LIKE users;

LIKE copies the full structure including indexes and auto-increment. To then copy data:

INSERT INTO users_copy SELECT * FROM users;

Partial Copy

CREATE TABLE active_users AS
SELECT * FROM users WHERE status = 'active';

PostgreSQL

Structure + Data

CREATE TABLE users_copy AS SELECT * FROM users;

Like MySQL, this does not copy indexes, constraints, or defaults.

Structure with Constraints

CREATE TABLE users_copy (LIKE users INCLUDING ALL);

INCLUDING ALL copies defaults, constraints, indexes, comments, and identity columns. You can be selective:

CREATE TABLE users_copy (LIKE users INCLUDING DEFAULTS INCLUDING CONSTRAINTS);

Then copy data separately:

INSERT INTO users_copy SELECT * FROM users;

SQL Server

Structure + Data

SELECT * INTO users_copy FROM users;

SELECT INTO creates the new table automatically. It copies column definitions and data but not indexes, constraints, or triggers.

Structure Only

SELECT * INTO users_copy FROM users WHERE 1 = 0;

The WHERE 1 = 0 ensures no rows are copied.

With Specific Columns or Filters

SELECT id, name, email INTO active_users
FROM users WHERE status = 'active';

Oracle

-- Structure + Data
CREATE TABLE users_copy AS SELECT * FROM users;

-- Structure Only
CREATE TABLE users_copy AS SELECT * FROM users WHERE 1 = 0;

To get the full DDL including indexes and constraints:

SELECT DBMS_METADATA.GET_DDL('TABLE', 'USERS') FROM DUAL;

Then edit the output to change the table name.

SQLite

-- Structure + Data
CREATE TABLE users_copy AS SELECT * FROM users;

-- Structure Only
CREATE TABLE users_copy AS SELECT * FROM users WHERE 0;

SQLite's CREATE TABLE AS does not copy primary keys or autoincrement.

What Gets Copied

Feature	CREATE AS SELECT	LIKE / Structure Copy
Column names & types	Yes	Yes
Data	Yes	No
Primary key	No	Yes (MySQL LIKE, PG INCLUDING ALL)
Indexes	No	Yes (MySQL LIKE, PG INCLUDING ALL)
Auto-increment	No	Yes (MySQL LIKE, PG INCLUDING ALL)
Foreign keys	No	No (must add manually)
Triggers	No	No
Constraints	No	Yes (PG INCLUDING ALL)

Using SQLPro Studio

In SQLPro for MSSQL, you can duplicate a table directly from the sidebar. Right-click a table and select "Duplicate Table" to create a copy with the structure and optionally the data — no SQL required.

---