Lesson 8.6

INSERT from a SELECT in SQL

Swap VALUES for a SELECT and one INSERT adds every row the query returns. Run the SELECT alone first: it is the preview.

Lesson 8.2 · Adding rows with INSERT typed one row's values by hand. When the new rows already exist as the answer to a query, write the query instead: INSERT takes a SELECT in place of VALUES and adds every row it returns.

Pattern

create or replace table price_watch (
  product_id   integer,
  product_name varchar,
  price        decimal(10, 2)
);

insert into price_watch (product_id, product_name, price)
select product_id, product_name, price
from products
where price > 25;

select * from price_watch order by product_id;

The SELECT is the preview

The habit from this chapter carries straight over. Before you add insert into on top, run the SELECT on its own. The rows it shows are exactly the rows that will be inserted, so a wrong filter is caught while it is still only a query.

Columns match by position

The first column the SELECT returns goes into the first column in the INSERT list, the second into the second, and so on. The names are not compared, so swapping two columns of the same type inserts the wrong data without an error. Always write the column list, and keep the two lists in the same order.

INSERT ... SELECT or CREATE TABLE AS

Lesson 18.4 · Stage, clean, and model builds a table straight from a query with CREATE TABLE ... AS SELECT. Use that to make a new table. Use INSERT ... SELECT to add rows to a table that already exists and keeps its own column types and constraints, such as a log or an archive that grows each time you run it.

Schema · Garden Shop Table · products 9 columns · 24 rows

Table · products

One row per product, with price, cost, and inventory levels.

9 columns · 24 rows

product_id int product_name text category_id int supplier_id int price decimal cost decimal quantity_on_hand int reorder_level int discontinued bool

Syntax reference

Need syntax details? See the SQL upsert reference

Your task

The starter creates an empty restock_list table. Fill it with one INSERT: every product whose quantity_on_hand is at or below its reorder_level and that is not discontinued, with order_quantity set to twice the reorder level minus what is on hand. Keep the final SELECT, which returns the list sorted by product_id.

Garden Shop · SQL engine loading · Ctrl/⌘ + Enter to run·

Run your query to inspect the result.

Expected answer

Columns: product_id, product_name, supplier_id, order_quantity.
Rows: 5 products, by product_id.
Bird Feeder is low too, but it is discontinued, so it stays off the list. Tomato Seeds need the most: 75.

Previous · 8.5 Transactions and ROLLBACK

Next · 8.7 UPDATE with CASE →

Reference

SQL Upsert Syntax

ON CONFLICT, INSERT OR REPLACE, and MERGE.

Mistake

Why Deduplicating Without ORDER BY Keeps the Wrong Row

ROW_NUMBER() needs an ORDER BY that says which row wins.

Reference

SQL CREATE TABLE AS SELECT

Store a query result as a table you can query again.

Reference