INSERT ... SELECT

Objectives

By the end of this lesson, you should be able to:

• Populate a table from the result of a SELECT, instead of literal values
• Explain when this is useful

💡 Why this matters: Sometimes the data you need to insert already exists, in another table, or as the result of some other query. INSERT ... SELECT copies it across directly, no need to read it out and type it back in as literal values.

⚠️ A note on verification: every statement and result in this lesson was run against a real, live PostgreSQL 18 database, using the employees table from Module 3, filtered with WHERE (covered fully in Module 6).

Copying Query Results Into a Table

CREATE TABLE engineering_employees (
    id INTEGER PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    salary NUMERIC(10,2)
);

INSERT INTO engineering_employees (id, first_name, last_name, salary)
SELECT id, first_name, last_name, salary FROM employees WHERE department = 'Engineering';

 id | first_name | last_name | salary
----+------------+-----------+---------
  1 | Erin       | Walsh     | 78000.00
  3 | Maya       | Kapoor    | 81000.00

Instead of VALUES (...), the INSERT is followed directly by a SELECT. Every row that SELECT returns becomes a row inserted into engineering_employees, matched up by position with the column list, exactly like a regular multi-row INSERT, except the values come from a live query instead of being typed out.

When This Is Useful

INSERT ... SELECT is common for tasks like archiving (copying old records into a _history table before deleting them), building a filtered or summarized copy of a table, or seeding a new table from an existing one, exactly like the engineering_employees example above. The source and destination don’t need identical columns, only the columns actually listed need to line up between the SELECT and the INSERT.

Try It

1. Create a high_earners table with id, first_name, and salary columns.
2. Use INSERT ... SELECT to populate it with every employee earning more than 70000 (WHERE salary > 70000).
3. Confirm the right rows landed in high_earners by selecting from it.

Recap

• INSERT INTO table (columns...) SELECT ... inserts the result of a query, instead of literal VALUES.
• Each returned row from the SELECT becomes one inserted row, matched by position to the column list.
• Useful for archiving, copying filtered subsets of data, or seeding a new table from an existing one.

Next lesson: UPDATE, changing data that’s already in a table.