Data Insertionlessonintermediate
INSERT via CTE and Subqueries
Dynamic INSERT using INSERT...SELECT, subqueries, and CTEs to avoid hardcoded values.
Overview
This lesson teaches students to write INSERT statements that do not rely on hardcoded IDs or static values. Instead, values are resolved dynamically using subqueries and CTEs. This is the foundation for writing rerunable (idempotent) scripts.
Lesson Plan
| Time | Activity |
|---|---|
| 0–10 min | Problem intro: why hardcoded IDs break scripts |
| 10–25 min | INSERT INTO ... SELECT ... — bulk insert from query result |
| 25–45 min | Using subqueries inside VALUES to resolve foreign keys dynamically |
| 45–60 min | CTE + INSERT pattern: prepare data in CTE, insert in main statement |
| 60–75 min | Guided practice: rewrite a hardcoded INSERT dynamically |
| 75–80 min | Wrap-up: rules for rerunable scripts |
Key Concepts
INSERT INTO t (col) SELECT col FROM source— insert result of a SELECT- Subquery in VALUES:
INSERT INTO t (fk) VALUES ((SELECT id FROM ref WHERE name = 'X')) - CTE before INSERT:
WITH cte AS (...) INSERT INTO t SELECT * FROM cte - Idempotency: script can run multiple times without duplicating data
ON CONFLICT DO NOTHINGfor safe re-runs- Resolving FK values by name/code instead of numeric ID
✏️Practice
Short in-class exercise:
- Insert a new film actor relationship by looking up IDs from names (no hardcoded IDs).
- Use a CTE to prepare a list of customers to insert into a
vip_customerstable.