INSERT INTO SELECT
Here is a simple, practical, and complete guide to the SQL INSERT INTO SELECT statement. This is a very useful way to copy or change data between tables.
What INSERT INTO SELECT Does
INSERT INTO … SELECT copies data from one table into another without using VALUES.
Useful for:
Data migration
Archiving
Transforming data into reporting tables
Copying filtered rows from one table to another
Basic Syntax (ANSI SQL)
target_tablealready exists- Column count and order must match
Basic Example — Copy all rows
Copy only filtered rows
Insert Rows with Transformations
- Perfect for building analytics or reporting tables.
Insert From JOIN
Database Engine Notes
PostgreSQL / MySQL / SQLite
Use the standard form:
SQL Server
Same syntax.
Supports additional options like OUTPUT, e.g.:
Important Rules & Pitfalls
1. The target table must already exist
Unless you use SELECT INTO (SQL Server) or CREATE TABLE AS (Postgres/MySQL).
2. Column count must match
If target has 3 columns, SELECT must output 3 columns.
3. Data types must be compatible
e.g., INT → INT, VARCHAR → TEXT, etc.
4. You can omit column list — but don’t unless safe
- Very dangerous if schemas differ.
Real-World Use Cases
1. Archive old orders
2. Migrate users to a new schema
3. Build a reporting table
Performance Tips
- Index your JOIN/WHERE columns
- Insert in batches for millions of rows
- Disable unnecessary triggers if possible (depends on DB)
- For huge migrations, consider
CREATE TABLE AS SELECTinstead
