INSERT from a query in SQL

The rows to insert can come from a query instead of a VALUES list.

INSERT INTO customer_totals (customer_id, lifetime_value)
SELECT
    customer_id,
    SUM(total)
FROM orders
WHERE status = 'paid'
GROUP BY customer_id;

How it works

1. Column lists line up by position, so the SELECT order matters.
2. This is how you backfill a table or snapshot a report.
3. No round trip through the client: the rows never leave the engine.

Keywords and builtins used here

• BY
• FROM
• GROUP
• INSERT
• INTO
• SELECT
• SUM
• WHERE

The run, in numbers

Lines

7

Characters to type

144

Tokens

25

Three-star pace

80 tpm

At the three-star pace of 80 tokens a minute, this run takes about 19 seconds.

Type this snippet

Step 5 of 8 in Tables & rows, step 18 of 23 in Language basics.

← Previous Next →