INSERT … VALUES adds the rows you type. To add rows that a query finds, put a SELECT where VALUES goes:
sql
INSERT INTO restock_list (product_id, name)
SELECT id, name
FROM products
WHERE stock = 0;SELECT id, name FROM products WHERE stock = 0the query
2 rows
restock_listthe target
restock_list
| product_id | name |
|---|---|
| 3 | Daemon Cat Plush |
| 9 | Holo Kraken Sticker |
Columns match by position
The first SELECT column goes into the first listed column, the second into the second, and so on. The names do not have to be the same.
- The SELECT must return one column for each listed column.
- Each value must fit the type and constraints of its target column.
- Run the SELECT alone first. Its result is exactly the rows the INSERT adds.
Add a fixed value
A SELECT list can hold a value that is not a column (Chapter 1: SELECT 6 * 7). Every row then gets that value:
sql
INSERT INTO mailing (email, name, source)
SELECT email, name, 'members'
FROM customers
WHERE is_member = true;Each new row has source = 'members'.