Lesson 20 of 61

5 mins read

SQL INSERT INTO SELECT: Bulk Data Migration

In Plain English

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.

The Mental Model

"`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

Target Must Exist: Destination table must be created beforehand with appropriate columns and constraints.

ETL Standard: Transfer, transform, and aggregate data between staging and warehouse tables.

Column Mapping: Column names do not need to match between tables; data types and position order must align.

Common Beginner Traps & How to Fix Them

Mistake: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`.

Pro Tip: In PostgreSQL and SQLite 3.24+, combine with `ON CONFLICT (id) DO UPDATE SET ...` for atomic "UPSERT" operations.

Live Interactive Example

Hit Run Code to see it live

Migrating Historical Orders with INSERT INTO SELECT

Python 3.12

Output Console

Click "Run Code" to view the rendered output.

How it works: The two completed orders were migrated into orders_archive while preserving data types.

Your Turn: Micro Challenge

No pressure! Edit the starter code below and test your solution with instant feedback.

Micro Exercise

Migrate Data with INSERT INTO SELECT

Insert all rows from `staging_leads` into `production_leads (id, email)`.
Select all from `production_leads`.

Sandbox Output

Click "Run & Check" to test your solution.

Finished reading and practicing?

Mark this lesson as completed to update your course progress.