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

CREATE TABLE users_backup AS
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

CREATE TABLE users_empty AS
SELECT *
FROM users
WHERE 1 = 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:

CREATE TABLE products (
  id INT PRIMARY KEY,
  name VARCHAR(100),
  price DECIMAL(10, 2)
);

Create a backup:

CREATE TABLE products_backup AS
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:

CREATE TABLE completed_orders AS
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:

CREATE TABLE test_users AS
SELECT *
FROM users
WHERE 1 = 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:

CREATE TABLE users_backup AS
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:

ALTER TABLE users_backup
ADD PRIMARY KEY (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:

CREATE TABLE users AS
SELECT * FROM users_backup;

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

Correct approach: Use a new name:

CREATE TABLE users_copy AS
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:

SELECT COUNT(*) 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