Introduction

Copying a table without its rows is a common database task when you need a staging table, a temporary working table, or a schema template for later inserts. The exact SQL depends on the database engine, and the important detail is that "copy the table" can mean columns only or a much richer clone that also preserves defaults, constraints, and indexes.

Decide what needs to be copied

Before picking a command, decide whether you need:

• column names and types only
• defaults and NOT NULL constraints
• primary keys and unique constraints
• indexes and foreign keys
• vendor-specific storage settings

Many quick SQL patterns copy only the columns. That is fine for ad hoc staging tables, but it is not the same thing as a faithful schema clone.

Common syntax by database engine

Different SQL engines solve this differently.

In PostgreSQL, a readable option is:

CREATE TABLE new_orders (LIKE orders INCLUDING ALL);

INCLUDING ALL copies much more than just the columns. If you want a narrower copy, PostgreSQL also lets you choose more specific INCLUDING options.

In MySQL, the usual command is:

CREATE TABLE new_orders LIKE orders;

That copies the table definition without copying the data.

In SQL Server, a common pattern is:

1SELECT *
2INTO new_orders
3FROM orders
4WHERE 1 = 0;

This creates a table with matching columns but does not automatically preserve every index or constraint from the source table.

A query-based portable pattern

Many systems support some form of "create a table from a query that returns no rows":

1CREATE TABLE new_orders AS
2SELECT *
3FROM orders
4WHERE 1 = 0;

This is useful because it is conceptually simple and widely understood. The downside is that it usually copies just the columns and inferred types, not the full schema behavior.

That makes it good for temporary or staging use, but risky when you actually need a production-quality duplicate.

Example staging-table workflow

Suppose you want a staging table for imports:

1CREATE TABLE orders_stage AS
2SELECT *
3FROM orders
4WHERE 1 = 0;

Later, you can load test rows into it:

INSERT INTO orders_stage (id, customer_id, total)
VALUES (1, 100, 49.99);

This is often enough when the table exists only to hold intermediate data and you do not need every production constraint.

Why the copied schema may not match exactly

This is where many people get surprised. A quick copy statement may omit:

• primary keys
• indexes
• foreign keys
• triggers
• identity or sequence behavior
• table ownership or permissions

If the new table will be used in application code or performance-sensitive queries, inspect the generated schema instead of assuming it matches the original.

Common Pitfalls

• Assuming every "copy table without data" statement preserves indexes and constraints.
• Using a query-based copy in production and later discovering important schema rules are missing.
• Forgetting that permissions, ownership, or identity behavior may not carry over.
• Reusing SQL examples across PostgreSQL, MySQL, SQL Server, and other engines without checking vendor-specific behavior.

Summary

• Copying a table without rows is database-specific.
• PostgreSQL and MySQL have schema-oriented commands, while query-based forms often copy columns only.
• 'CREATE TABLE AS SELECT ... WHERE 1 = 0 is useful for staging tables but not always for full schema duplication.'
• Decide up front whether you need columns only or a faithful schema clone with constraints and indexes.