INSERT INTO … SELECT

The team analyst wants a separate top_batters table containing only players with 700+ runs. You could re-type those rows… or let SQL copy them for you. INSERT INTO … SELECT copies the result of a query straight into another table.

Definition

INSERT INTO … SELECT is a form of INSERT that adds the rows returned by a SELECT query into an existing target table. Instead of typing values, the SELECT reads rows from a source table, and the INSERT writes them into the target.

• Existing target table — the target must already exist (created earlier with CREATE TABLE).
• Rows returned by SELECT — any SELECT works: with WHERE, JOIN, calculations, etc. Zero rows returned → zero rows inserted.
• Source is unchanged — copying doesn't remove anything from the source table.

Syntax

INSERT INTO target_table (column1, column2, ...)
SELECT columnA, columnB, ...
FROM source_table
WHERE condition;

Note: There is no VALUES keyword — the SELECT takes its place. The columns are matched by position, not by name.

Example — copy only players with runs ≥ 700

Source: players (6 rows) → Target: top_batters (empty, columns id, name, runs)

CREATE TABLE top_batters (
    id   INT,
    name VARCHAR(50),
    runs INT
);

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

SELECT * FROM top_batters;

Target after — only runs ≥ 700 copied:

Step by step: 1️⃣ SELECT reads the source and keeps rows with runs >= 700 → 2️⃣ INSERT writes those 3 rows into the target. Rohit (500), Dhoni (350) and Bumrah (20) are skipped. SQL Server reports (3 rows affected).

Execution order: FROM players → WHERE runs >= 700 → SELECT id, name, runs → INSERT INTO top_batters.

Example — copy with a JOIN and a calculation

The SELECT can be as rich as you like:

CREATE TABLE player_cards (
    name      VARCHAR(50),
    team_name VARCHAR(50),
    city      VARCHAR(30)
);

INSERT INTO player_cards (name, team_name, city)
SELECT p.name, t.team_name, t.city
FROM players AS p
INNER JOIN teams AS t
    ON p.team = t.team
WHERE p.country = 'India';

INSERT … VALUES vs INSERT … SELECT

Common mistakes

• ❌ Adding VALUES: INSERT INTO t VALUES SELECT … ✅ No VALUES — write the SELECT directly.
• ❌ Different number of columns: target lists 3, SELECT returns 5. ✅ Same count, same order.
• ❌ Assuming columns match by name. ✅ They match by position — SELECT name, id would put names into id!
• ❌ Target table doesn't exist. ✅ Create it first — or use SELECT … INTO (SQL Server) which creates it for you.
• ❌ Running it twice → duplicate rows in the target. ✅ Clear the target first or filter out rows that already exist.

Interview tip

Q: How do you copy rows from one existing table to another? A: INSERT INTO target (cols) SELECT cols FROM source WHERE …. If the target doesn't exist yet, SQL Server's SELECT … INTO new_table FROM source creates and fills it in one step.