How to Copy a Table in SQL

What you’ll build or solve

You’ll create a new table based on an existing one.

When this approach works best

Copying a table works best when you:

• Create a backup before large updates
• Duplicate production data for testing
• Archive records into a separate table

For example, you might copy users to users_backup, clone orders before running bulk updates, or create a snapshot of products for reporting.

This is a bad idea if you only need a few rows or columns. In that case, write a focused SELECT query instead of copying the entire table.

Prerequisites

• Access to the database
• Permission to create tables
• Basic SQL knowledge

Step-by-step instructions

Step 1: Copy structure and data into a new table

The most common method uses CREATE TABLE ... AS SELECT or its equivalent.

MySQL or PostgreSQL

CREATETABLE users_backupAS
SELECT*FROM users;

This creates users_backup and copies all rows from users.

SQL Server

SELECT*
INTO users_backup
FROM users;

This also creates a new table and copies the data in one step.

Both approaches create a new table with the same columns and rows as the source table.

What to look for

• Indexes and constraints are usually not copied
• The new table name must not already exist
• Data types are copied from the source table

Step 2: Copy structure only

If you want the table definition but no rows, add a condition that returns no results.

MySQL or PostgreSQL

CREATETABLE users_emptyAS
SELECT*
FROM users
WHERE1=0;

The WHERE 1 = 0 clause prevents any rows from being inserted, but the column structure is created.

What to look for

• In MySQL, CREATE TABLE new_table LIKE old_table; copies structure and indexes, but this is a different command
• To copy into an existing table, use INSERT INTO destination SELECT * FROM source;, which is a different operation
• If you need indexes or foreign keys, recreate them manually after copying
• Watch for primary key conflicts if you later insert additional data

Examples you can copy

Example 1: Quick backup before changes

You have a products table:

CREATETABLE products (
  idINTPRIMARYKEY,
  nameVARCHAR(100),
  priceDECIMAL(10,2)
);

Create a backup:

CREATETABLE products_backupAS
SELECT*FROM products;

Now you can update products while keeping the original data safe.

Example 2: Copy only completed orders

You want a snapshot of completed orders:

CREATETABLE completed_ordersAS
SELECT*
FROM orders
WHERE status='completed';

This creates a new table that contains only the matching rows.

Example 3: Prepare an empty test table

You want to test schema changes without real data:

CREATETABLE test_usersAS
SELECT*
FROM users
WHERE1=0;

You now have the same structure without copying any records.

Common mistakes and how to fix them

Mistake 1: Expecting indexes and constraints to copy automatically

What you might do:

CREATETABLE users_backupAS
SELECT*FROM users;

Why it breaks expectations: This usually copies only columns and data. Primary keys, foreign keys, and indexes are not transferred.

Correct approach: Recreate constraints manually:

ALTERTABLE users_backup
ADDPRIMARYKEY (id);

Or use database-specific cloning features if you need a full structural copy.

Mistake 2: Overwriting an existing table name

What you might do:

CREATETABLE usersAS
SELECT*FROM users_backup;

Why it fails: If users already exists, the database returns an error.

Correct approach: Use a new name:

CREATETABLE users_copyAS
SELECT*FROM users;

Or drop the old table first if that fits your workflow.

Mistake 3: Ignoring data size

What you might do: Copy a very large table without checking its size.

Why it causes issues: The operation can lock tables or consume significant storage.

Correct approach: Estimate row count first:

SELECTCOUNT(*)FROM users;

Plan disk space and timing before copying large datasets.

Troubleshooting

• If you see “permission denied,” confirm you have CREATE TABLE privileges.
• If you see “table already exists,” choose a different name or drop the existing table.
• If the copy seems incomplete, verify your SELECT condition does not filter out rows.
• If constraints are missing, recreate them manually after copying.

Quick recap

• Use CREATE TABLE ... AS SELECT to copy structure and data
• Use SELECT ... INTO in SQL Server
• Add WHERE 1 = 0 to copy structure only
• Indexes and constraints are usually not copied
• Use a new table name that does not already exist
• Plan for storage and large data sizes