Cloning Tables With and Without Data in SQL Server

1. Introduction

Cloning a table in T-SQL sounds simple: copy one table into another. In practice, there are different kinds of copies and each behaves in its own way.

Sometimes you need a table that looks the same and holds the same rows. Other times, you only want the structure for a test or a staging table. On top of that, you may care about keys, indexes, identity columns, and constraints.

This guide focuses on T-SQL table cloning in SQL Server, including current versions like SQL Server 2022 and later. The basic patterns still work in older versions.

You will see clear examples for three core needs: copying a table with data, copying structure without data, and copying the full schema details such as keys and indexes. The goal is simple scripts you can drop into a query window and understand at a glance.

2. What Does It Mean To Clone a Table in T-SQL?

In plain terms, cloning a table means creating a new table that is based on an existing one. You might copy only the design, or you might copy both design and rows.

In real work, this shows up all the time. You build quick backups before a risky update. You create test tables for new code. You prepare staging tables for ETL. You set up reporting tables that read from a live source.

There are three layers to keep in mind:

• Structure: the column names and data types.
• Data: the actual rows.
• Schema details: keys, indexes, identities, defaults, and constraints.

Different methods copy different layers. SELECT INTO gives you a fast copy of structure and data but skips indexes and keys. The CLONE feature in SQL Server 2022 copies structure plus many schema details but not data. SSMS scripts can copy almost everything.

Once you know what each tool brings over, you can pick the right method instead of guessing.

Structure vs data vs schema: what gets copied?

When you clone a table, you might care about some or all of these pieces:

• Columns and data types: the basic design of the table.
• Data rows: every row in the table or a subset of rows.
• Indexes: clustered and nonclustered indexes that help performance.
• Primary keys: the main unique key for each row.
• Foreign keys: links to other tables.
• Default constraints: values that fill in when a column is not provided.
• Check constraints: rules that validate data values.
• Identity property: auto-increment behavior for integer keys.
• Triggers: T-SQL code that fires on INSERT, UPDATE, or DELETE.

Most quick methods only copy the columns and data. They skip indexes, keys, constraints, identity settings, and triggers. You have to add those back later if you need them.

When you should clone tables in SQL Server

Cloning tables is not just for database admins. It is a core skill for any SQL Server developer or analyst. Common uses include:

• Building a test table from a production structure so you can try new code safely.
• Trying a risky data change, such as a batch update or delete, after taking a quick copy.
• Creating a reporting table that mirrors a live table but holds a filtered or reshaped dataset.
• Taking a quick backup before a migration between schemas or databases.
• Creating a staging table in ETL that matches the target table but starts empty.

If you know why you are cloning, you can decide whether you need only structure, a full data copy, or a richer clone with schema details.

3. How To Clone Tables With Data

When you want a new table that already holds rows, you have two main T-SQL patterns. One is fast and simple. The other gives you more control.

Using SELECT INTO for a quick full copy of data

SELECT INTO creates a new table and fills it in one step. It is one of the fastest ways to copy structure and data.

Copy the entire table:

SELECT *
INTO SalesArchive
FROM Sales;

This creates a new table SalesArchive with the same columns as Sales, then copies all rows with the data.

Copy only some rows with a filter:

SELECT *
INTO Sales2024
FROM Sales
WHERE OrderDate >= '2024-01-01'
  AND OrderDate < '2025-01-01';

Now Sales2024 holds only the rows for that year.

What SELECT INTO does not copy:

• No indexes.
• No primary or foreign keys.
• No default or check constraints.
• No triggers.
• No identity property.

If the source table has an identity column, SELECT INTO copies the values, but the new column is a normal column, not an identity column. If you insert more rows later, SQL Server will not auto-increment that column unless you redefine it.

Use SELECT INTO when you want speed and a basic copy, for example temp tables and quick backups.

Using CREATE TABLE plus INSERT INTO SELECT for more control

Sometimes you want control over the new table design. Maybe you need fewer columns, different names, or different data types. In that case, create the table first, then insert data.

Step 1, create the table:

CREATE TABLE SalesCopy
(
    SalesId      INT          NOT NULL,
    OrderDate    DATE         NOT NULL,
    CustomerId   INT          NOT NULL,
    TotalAmount  DECIMAL(18,2) NULL
);

Step 2, insert data from the source:

INSERT INTO SalesCopy (SalesId, OrderDate, CustomerId, TotalAmount)
SELECT SalesId, OrderDate, CustomerId, TotalAmount
FROM Sales;

You can also pick only some columns:

INSERT INTO SalesCopy (SalesId, OrderDate)
SELECT SalesId, OrderDate
FROM Sales;

This pattern is helpful when:

• You need different column names.
• You want to change data types, such as DATETIME to DATE.
• You only want a subset of columns.
• You plan to add your own keys and indexes at the same time.

It is a bit more work than SELECT INTO, but you get full control over the schema.

Handling identity columns when copying data

An identity column is a column that auto-increments, for example a CustomerId that grows by 1 each time you insert a row.

If you want to keep the original identity values in a clone, and the target table has an identity column, you may need IDENTITY_INSERT.

Example:

CREATE TABLE CustomerCopy
(
    CustomerId INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
    Name       NVARCHAR(100)     NOT NULL
);

SET IDENTITY_INSERT CustomerCopy ON;

INSERT INTO CustomerCopy (CustomerId, Name)
SELECT CustomerId, Name
FROM Customer;

SET IDENTITY_INSERT CustomerCopy OFF;

Key points:

• You must list all columns in the INSERT when IDENTITY_INSERT is ON.
• Only one table in a database can have IDENTITY_INSERT set to ON at a time.
• Turn it OFF as soon as you finish.

This pattern gives you a true clone of identity values when you need one.

4. Clone Without Data

Often you only want the structure of a table. This is common in development and ETL work. The table starts empty, but matches the source schema.

You have classic tricks that work in all recent versions, plus a newer CLONE feature in SQL Server 2022 and later.

Copy structure only with SELECT TOP 0

Many SQL platforms use CREATE TABLE NewTable AS SELECT .... In SQL Server, you get the same effect with

Example:

SELECT TOP 0 *
INTO SalesTemplate
FROM Sales;

SalesTemplate now exists with the same columns and data types as Sales, but it holds zero rows.

You can also pick specific columns:

SELECT TOP 0
    SalesId,
    OrderDate,
    CustomerId
INTO SalesTemplateLite
FROM Sales;

This pattern:

• Copies the columns and types.
• Does not copy any rows.
• Does not copy indexes, keys, constraints, or triggers.
• Does not preserve the identity property.

Use it when you just want a shell table for staging, unit tests, or scratch work, and you will handle indexes and constraints later if needed.

Cloning structure in SQL Server 2022+ with CREATE TABLE AS CLONE OF

SQL Server 2022 added a powerful feature called table cloning. It creates a metadata clone, which means it copies the definition, not the data.

Basic syntax:

CREATE TABLE SalesClone
AS CLONE OF Sales;

SalesClone is a new, empty table. It matches Sales in many important ways.

What CLONE copies:

• All columns and data types.
• Primary and unique keys.
• Index definitions.
• Identity property.
• Default constraints.
• Check constraints.

What CLONE does not copy:

• Data rows.
• Triggers.

Since it only copies metadata, it is very fast, even for very large tables.

Typical uses:

• Testing query changes on a structure that matches a big production table.
• Trying index changes on a safe copy.
• Building an empty table with full schema for later use, then inserting data.

Permissions are simple. You need SELECT on the source table and CREATE TABLE in the target schema. If you can read the table and create tables in that database, you are ready to use CLONE.

5. Copying Keys, Indexes, and Constraints When Cloning Tables

The simple methods like SELECT INTO and SELECT TOP 0 INTO skip keys and indexes. That is fine for quick work, but sometimes you really need a closer one-to-one clone.

You have two main options: let SSMS generate a full script, or add only the pieces you care about by hand.

Using SSMS Generate Scripts for a full table clone

SQL Server Management Studio (SSMS) can script a table with its full schema and, if you want, its data.

At a high level:

1. In Object Explorer, right-click the database.
2. Choose Tasks, then Generate Scripts.
3. Pick the table or tables you want to clone.
4. In the options, choose to script schema only, or schema and data.
5. Script to a new query window or a file.

The generated script can include:

• CREATE TABLE with all column definitions.
• Primary keys and foreign keys.
• Indexes.
• Default and check constraints.
• Triggers, if you select them.
• INSERT statements for all rows, if you choose schema and data.

This is a strong choice when you need to move a table to another database or server, or when you want a full copy with all behavior intact. You can review the script, change names or schemas, and then run it in the target database.

Adding indexes and constraints by hand after cloning

Sometimes you are happy with a quick SELECT INTO clone, but you still want a few keys or indexes. In that case, you can add them with simple statements.

Example, add a primary key:

ALTER TABLE SalesCopy
ADD CONSTRAINT PK_SalesCopy_SalesId
PRIMARY KEY CLUSTERED (SalesId);

Add a nonclustered index:

CREATE NONCLUSTERED INDEX IX_SalesCopy_CustomerId
ON SalesCopy (CustomerId);

Add a check constraint:

ALTER TABLE SalesCopy
ADD CONSTRAINT CK_SalesCopy_TotalAmount
CHECK (TotalAmount >= 0);

Add a default constraint:

ALTER TABLE SalesCopy
ADD CONSTRAINT DF_SalesCopy_OrderDate
DEFAULT (GETDATE()) FOR OrderDate;

Good practice is to inspect the original table definition, for example by scripting it in SSMS, then recreate only the pieces you really need on the clone. This keeps performance and storage under control, instead of blindly copying every index.

6. Method Comparison at a Glance

Here is a quick comparison of common cloning methods:

Use this as a mental checklist when you pick your approach.