Copy A Table In Sql Server

6 min read

Copy a Table in SQL Server: Complete Guide to Every Method

Copying a table in SQL Server is one of those everyday database tasks that looks trivial until you actually need it — a developer wants a sandbox copy of dbo.Orders, a DBA needs a pre-deployment backup of a critical table, or an analyst needs to restructure a table without touching production data. And the good news is that SQL Server gives you several reliable ways to copy a table in SQL Server, each with different tradeoffs around speed, structure preservation, and storage. This guide walks through all of them, from the fastest one-liner to scripted clones that keep every index and constraint intact.

Why You Might Need to Copy a Table

Before diving into syntax, it helps to know which scenario you are in, because the right method depends entirely on your goal:

  • Creating a backup snapshot before a risky UPDATE or DELETE.
  • Cloning production data into a development or staging database.
  • Restructuring a table — for example, changing a column's data type or splitting a wide table.
  • Archiving historical rows into a separate reporting table.
  • Building a test harness for query tuning, where you need realistic row counts and distributions.
  • Duplicating structure only, to create an empty table with the same shape.

The single most important distinction is whether you need data only, structure only, or both structure and data with all database objects.

Method 1: SELECT INTO — Copy Both Data and Structure

The fastest and most commonly used approach is SELECT ... INTO. It creates a brand-new table and populates it in one statement.

-- Copy the entire table into a new table in the same database
SELECT *
INTO dbo.Orders_Backup_20260826
FROM dbo.Orders;

You can also copy a subset of rows and columns:

SELECT OrderID, CustomerID, OrderDate, TotalAmount
INTO dbo.Orders_2026
FROM dbo.Orders
WHERE OrderDate >= '2026-01-01';

Or copy across databases on the same instance using three-part naming:

SELECT *
INTO AdventureWorks2022.dbo.Orders_Copy
FROM WideWorldImporters.dbo.Orders;

What SELECT INTO Actually Copies

This is where most surprises happen. SELECT INTO copies data, column names, data types, nullability, and the IDENTITY property. It does not copy:

  1. Primary keys, foreign keys, unique constraints, or check constraints
  2. Indexes of any kind
  3. Default constraints
  4. Triggers
  5. Computed columns as computed columns — they become plain columns holding values
  6. Statistics
  7. Permissions, extended properties, and filegroup placement (the new table lands on the default filegroup)
  8. Memory-optimized table characteristics — SELECT INTO is not supported for memory-optimized tables

A practical consequence: your copy has no clustered index, which means it is a heap. That is usually fine for a temporary backup, but it will perform poorly for large analytical queries until you add indexes.

The IDENTITY Trap

If the source table has an identity column, the new table inherits the identity property and the seed continues from the highest copied value. If you then use INSERT INTO ... SELECT to add more rows into a table that already has an identity column, you must toggle IDENTITY_INSERT:

SET IDENTITY_INSERT dbo.Orders_Copy ON;

INSERT INTO dbo.Orders_Copy (OrderID, CustomerID, OrderDate)
SELECT OrderID, CustomerID, OrderDate
FROM dbo.Orders
WHERE OrderDate < '2026-01-01';

SET IDENTITY_INSERT dbo.Orders_Copy OFF;

Method 2: INSERT INTO ... SELECT — Append Into an Existing Table

When the target table already exists, use INSERT INTO ... On top of that, sELECT. This is the standard approach for archiving, merging, or loading data into a pre-created table that already has the correct constraints and indexes Small thing, real impact..

INSERT INTO dbo.Orders_Archive (OrderID, CustomerID, OrderDate, TotalAmount)
SELECT OrderID, CustomerID, OrderDate, TotalAmount
FROM dbo.Orders
WHERE OrderDate < '2020-01-01';

Always list the columns explicitly. Relying on ordinal position breaks the moment someone reorders or adds a column in either table, and it can silently load data into the wrong columns Small thing, real impact. Simple as that..

For large loads, the TABLOCK hint enables minimal logging under the simple or bulk-logged recovery models and reduces lock escalation overhead:

INSERT INTO dbo.Orders_Archive WITH (TABLOCK)
SELECT OrderID, CustomerID, OrderDate, TotalAmount
FROM dbo.Orders
WHERE OrderDate < '2020-01-01';

Note that minimal logging requires the target to be a heap with no indexes, or a clustered index with no nonclustered indexes and a TABLOCK hint. Under the full recovery model, every row is fully logged regardless Practical, not theoretical..

Method 3: Copy Structure Only

If you need an empty table with the same shape, SELECT INTO will not help because it always inserts rows. Two reliable options:

Filter everything out:

SELECT *
INTO dbo.Orders_Empty
FROM dbo.Orders
WHERE 1 = 0;   -- always false,

-- always false, so no rows survive the filter. The metadata is still copied.

**Use the catalog to build the DDL:**

```sql
SELECT * INTO dbo.Orders_Empty
FROM dbo.Orders
WHERE 1 = 0;

-- or, without touching data at all:
SELECT TOP 0 *
INTO dbo.Orders_Empty
FROM dbo.Orders;

Both produce an empty heap with the same column names, types, nullability, and identity property. Neither brings indexes, constraints, defaults, or triggers. If you need those, generate the DDL from metadata or script the table out of SSMS (Script Table As → CREATE To → New Query Editor Window) and change the name Nothing fancy..

This is where a lot of people lose the thread The details matter here..

A minimal metadata-driven generator, useful when you cannot open SSMS:

DECLARE @sql NVARCHAR(MAX) = N'';

SELECT @sql +=
    N',' + QUOTENAME(c.Still, name) + N' ' + t. And name +
    CASE WHEN t. name IN ('char','varchar','nchar','nvarchar','binary','varbinary')
         THEN N'(' + CASE WHEN c.In practice, max_length = -1 THEN N'MAX'
                          ELSE CAST(c. max_length /
                               CASE WHEN t.Worth adding: name LIKE 'n%' THEN 2 ELSE 1 END AS VARCHAR(10))
                     END + N')'
         ELSE N'' END +
    CASE WHEN c. Because of that, is_nullable = 0 THEN N' NOT NULL' ELSE N' NULL' END +
    CASE WHEN c. Think about it: is_identity = 1 THEN N' IDENTITY(1,1)' ELSE N'' END
FROM sys. columns c
JOIN sys.Here's the thing — types t ON t. user_type_id = c.Because of that, user_type_id
WHERE c. But object_id = OBJECT_ID(N'dbo. Orders')
ORDER BY c.

SET @sql = N'CREATE TABLE dbo.Orders_Empty (' + STUFF(@sql, 1, 1, N'') + N');';
PRINT @sql;   -- review before executing
-- EXEC sys.sp_executesql @sql;

Choosing a Method

Goal Method Speed Carries indexes/constraints
New table + data, ad hoc SELECT INTO Fastest (minimal logging) No
Append into existing table INSERT INTO ... SELECT Good with TABLOCK Yes (already there)
Empty table, same shape SELECT INTO ... WHERE 1=0 Instant No
Production clone, exact copy Scripted DDL + INSERT Slower, safest Yes

Rule of thumb: SELECT INTO for throwaway work, scripted DDL plus INSERT INTO ... SELECT whenever the copy has to behave like the original No workaround needed..

Gotchas Worth Checking Before You Run

  • Computed and persisted columns. SELECT INTO materializes computed columns as plain columns holding values. The expression is gone, so the copy will not recompute if base data changes.
  • Constraints and defaults. None are copied. Your clone will happily accept an order total of -500 or a NULL customer.
  • Indexes and statistics. Statistics are built for the new columns, but nonclustered indexes are not created. Add them after the load, not before — building indexes on
Hot Off the Press

Just Went Live

Picked for You

We Thought You'd Like These

Thank you for reading about Copy A Table In Sql Server. We hope the information has been useful. Feel free to contact us if you have any questions. See you next time — don't forget to bookmark!
⌂ Back to Home