SQL INSERT INTO SELECT

The previous lesson's SELECT INTO only ever creates a brand-new table. INSERT INTO SELECT solves the opposite, very common, need: copying rows from a query into a table that already exists, and unlike SELECT INTO, its syntax is identical across every database in this course.

Learning Objectives

Use INSERT INTO SELECT to copy rows into an existing table.
Match the SELECT list's columns and order to the target table's INSERT list.
Explain how this differs from SELECT INTO / CREATE TABLE AS SELECT.

Basic INSERT INTO SELECT

high_earners must already exist, created ahead of time, with matching column types. The INSERT INTO column list and the SELECT column list have to line up, same number of columns, same order, compatible types, exactly the same rule UNION enforced back in Module 5.

MySQL / MariaDB / PostgreSQL / SQLite / SQL Server / Oracle

INSERT INTO high_earners (first_name, salary)
SELECT first_name, salary
FROM employees
WHERE salary > 90000;

Copying Into a Related Table

A genuinely practical use: assigning every Engineering employee to project 1 (Warehouse Bot v2) in one statement instead of one INSERT per employee. The literal 1 alongside e.id in the SELECT list is perfectly valid, a query's SELECT list can mix columns and fixed values freely.

MySQL / MariaDB / PostgreSQL / SQLite / SQL Server / Oracle

INSERT INTO project_assignments (employee_id, project_id)
SELECT e.id, 1
FROM employees e
WHERE e.department_id = 1;

Try It Yourself

Write a statement that inserts every employee's first_name and salary, for employees earning over 85,000, into an already-existing top_performers table.

Answer

INSERT INTO top_performers (first_name, salary)
SELECT first_name, salary
FROM employees
WHERE salary > 85000;

Frequently Asked Questions

Does the target table need to have the same column names as the source?

No, column names only need to match on the target table's own INSERT INTO (...) list, the SELECT side can use entirely different source column names, or expressions, as long as the count and types line up in order.

Can INSERT INTO SELECT trigger constraint violations?

Yes, exactly like a normal INSERT. A duplicate primary key, a NOT NULL violation, or an invalid foreign key in the copied data fails the statement the same way a single-row INSERT would.

Common Beginner Mistakes

Confusing this with SELECT INTO from the previous lesson

SELECT INTO (or CREATE TABLE AS SELECT) creates a brand-new table. INSERT INTO SELECT requires the target table to already exist. Using the wrong one against an existing table name causes an error either way, a table-already-exists error for SELECT INTO, or a table-doesn't-exist error for INSERT INTO SELECT if it's missing.

Mismatched column counts between the INSERT list and the SELECT list

Same hard error as UNION's column rule, both lists must have the same number of columns, in a compatible order and type.

Best Practices

Always list the target table's columns explicitly, INSERT INTO table (col1, col2), rather than relying on positional column order alone.
Double-check the SELECT list's column order matches the INSERT list's order exactly, position matters, not names.
Use a WHERE clause to scope exactly which rows get copied, an unfiltered SELECT * copies everything.

Interview Questions

What's the difference between SELECT INTO and INSERT INTO SELECT?

SELECT INTO (SQL Server) and CREATE TABLE AS SELECT (every other database here) both create a brand-new table from a query's results. INSERT INTO SELECT instead copies a query's results into a table that already exists, and its syntax is identical across every database in this course.

Write a statement that assigns every Research department employee to project 2.

INSERT INTO project_assignments (employee_id, project_id) SELECT e.id, 2 FROM employees e WHERE e.department_id = 2; — the SELECT list mixes a real column (e.id) with a fixed literal value (2), both valid in an INSERT INTO SELECT's source query.

Summary

INSERT INTO SELECT copies a query's results into an already-existing table, with the same column-count-and-type matching rule UNION uses, and unlike SELECT INTO, it's portable syntax everywhere in this course.

What's Next?

CASE next, SQL's if/else, for computing a value conditionally right inside SELECT, WHERE, or ORDER BY.

</>

Ready to Practice SQL?

Write and run real SQL queries online, right in your browser, no install or signup required. Pick a playground based on which database you want to practice on.

SQLite / PostgreSQL

Fast, no server, no ads.

Open DB Fiddle ↗

MySQL / SQL Server

Switch databases with one dropdown.

Open SQL Fiddle ↗

Both open a third-party SQL playground in a new tab. External site terms and privacy policy apply.