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
UPDATEorDELETE. - 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:
- Primary keys, foreign keys, unique constraints, or check constraints
- Indexes of any kind
- Default constraints
- Triggers
- Computed columns as computed columns — they become plain columns holding values
- Statistics
- Permissions, extended properties, and filegroup placement (the new table lands on the default filegroup)
- Memory-optimized table characteristics —
SELECT INTOis 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 INTOmaterializes 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
-500or aNULLcustomer. - 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