SQL to Copy a Table from One Database to Another

A common task when setting up a dev or test environment is copying tables from production (or a staging database) without going through a full backup and restore. SQL Server’s SELECT INTO syntax handles this in a single statement.

The pattern

sql

SELECT *
INTO TargetDB.dbo.Table1
FROM SourceDB.dbo.Table1

Both databases need to be on the same SQL Server instance. The statement creates the destination table and populates it with all rows from the source in one go.

To copy multiple tables:

sql

SELECT *
INTO DB1.dbo.Table1
FROM DevDB1.dbo.Table1

SELECT *
INTO DB1.dbo.Table2
FROM DevDB1.dbo.Table2

What it doesn’t copy

SELECT INTO creates the table structure and copies the data, but it does not bring across:

Primary keys and constraints
Default values
Indexes
Foreign keys
Identity column properties

For a quick dev copy this is usually fine. If you need the full structure — constraints, indexes and all — script the table definition from the source first (right-click the table in SSMS → Script Table As → CREATE To), run that against the target database to create the empty table, then use INSERT INTO ... SELECT to populate it:

sql

INSERT INTO DB1.dbo.Table1
SELECT * FROM DevDB1.dbo.Table1

This way the table already exists with its full definition and you’re only copying the rows.