Python SQL INSERT INTO SELECT: Bulk Data Migration
The `INSERT INTO SELECT` statement copies data from one table and inserts it into an EXISTING destination table. Unlike `SELECT INTO`, the target table must already exist, allowing you to append new transformed rows while preserving all target table constraints.
"`INSERT INTO SELECT` is like funneling water from multiple small buckets into a large existing reservoir."
Deep Dive: How It Works
Syntax: `INSERT INTO target_table (col1, col2) SELECT colA, colB FROM source_table WHERE condition;`.
Data Type Compatibility: The number, sequence, and data types of projected SELECT columns must match the target INSERT columns.
ETL & Archiving: The standard pattern for nightly ETL data warehouse loading (e.g. archiving processed events from `events_stream` into `events_history`).
Syntax Blueprint
INSERT INTO existing_archive_table (user_id, total_spent) SELECT id, lifetime_value FROM current_users WHERE active = 0;
Transfer query results directly into an existing target table.
Core Rules to Remember
Common Beginner Traps & How to Fix Them
Inserting duplicate primary keys into the target table and triggering a constraint violation.Why it happens: Target table already has rows with matching IDs.
How to fix: Filter out existing IDs with `WHERE id NOT IN (SELECT id FROM target_table)` or use `INSERT OR IGNORE` / `ON CONFLICT DO NOTHING`.
Live Interactive Example
Hit Run Code to see it liveYour Turn: Micro Challenge
No pressure! Edit the starter code below and test your solution with instant feedback.
Migrate Data with INSERT INTO SELECT
- Insert all rows from `staging_leads` into `production_leads (id, email)`.
- Select all from `production_leads`.
Finished reading and practicing?
Mark this lesson as completed to update your course progress.