SELECT INTO — Copy into a New Table

Before a risky update, a careful developer makes a backup copy of the table. In SQL Server you can create that copy — structure and data — in a single statement: SELECT … INTO.

Definition

SELECT … INTO is a SQL Server (T-SQL) statement that creates a brand-new table and fills it with the rows returned by a SELECT query — in one step. The new table's columns and data types are taken from the query's result.

• Brand-new table — the target must not exist yet (otherwise: "There is already an object named … in the database").
• Columns from the query — names and data types come from the SELECT list (use aliases to name calculated columns).
• One step — no separate CREATE TABLE needed.

Syntax

SELECT column1, column2, ...
INTO new_table
FROM source_table
WHERE condition;

Example — back up the players table

SELECT *
INTO players_backup
FROM players;

SQL Server reports (6 rows affected) and players_backup now exists with the same 6 rows and the same column names and types.

Example — copy a filtered subset

SELECT id, name, runs
INTO top_batters
FROM players
WHERE runs >= 700;

SELECT * FROM top_batters;

What is NOT copied

SELECT INTO copies columns, data types, NULL-ability and data (and an IDENTITY property in simple cases). It does not copy:

• the PRIMARY KEY, UNIQUE, CHECK, FOREIGN KEY or DEFAULT constraints,
• indexes or triggers.

So a backup made with SELECT INTO is a plain table — add a primary key afterwards if you need one.

Tricks

-- Copy only the STRUCTURE (an empty table): a WHERE that is never true
SELECT *
INTO players_empty
FROM players
WHERE 1 = 0;

-- Copy into a temporary table (#) that disappears when your session ends
SELECT *
INTO #indian_players
FROM players
WHERE country = 'India';

SELECT INTO vs INSERT INTO … SELECT

SQL Server vs MySQL / PostgreSQL

Note (playground): The browser playground doesn't support SELECT … INTO. The runnable example below shows the same idea with CREATE TABLE + INSERT INTO … SELECT, which also works in SQL Server.

Common mistakes

• ❌ Running SELECT INTO when the target already exists → error. ✅ Use a new name, DROP TABLE IF EXISTS first, or use INSERT INTO … SELECT.
• ❌ Expecting the primary key to be copied. ✅ Add it afterwards with ALTER TABLE … ADD CONSTRAINT.
• ❌ Putting INTO in the wrong place (SELECT * FROM players INTO backup). ✅ INTO comes right after the column list, before FROM.
• ❌ Unnamed calculated columns: SELECT runs * 2 INTO … → error. ✅ Give an alias: runs * 2 AS double_runs.

Interview tip

Q: What's the difference between SELECT INTO and INSERT INTO SELECT? A: SELECT INTO creates a new table from the query result (it must not exist), copying columns and data but not constraints or indexes. INSERT INTO … SELECT adds rows to a table that already exists.