SQL INSERT INTO SELECT – Copy Data Between Tables Safely
Introduction – What is SQL INSERT INTO SELECT?
The INSERT INTO SELECT statement is used to insert rows from one table or query into an existing table. It’s a powerful method for copying data across tables with matching structures.
In this guide, you’ll learn:
- Syntax of
INSERT INTO SELECT - Difference from
SELECT INTO - Examples with matching and partial column inserts
- Performance and best practices
1. INSERT INTO SELECT Syntax
INSERT INTO target_table (col1, col2)
SELECT col1, col2
FROM source_table
WHERE condition;
Inserts query results into an existing table.
2. Example – Copy Completed Orders to Archive
INSERT INTO archived_orders (id, customer_id, order_date)
SELECT id, customer_id, order_date
FROM orders
WHERE status = 'completed';
Moves completed orders into an archive table.
3. INSERT INTO SELECT vs SELECT INTO
| Operation | Description | Table Must Exist? |
|---|---|---|
INSERT INTO SELECT | Appends rows into an existing table | Yes |
SELECT INTO | Creates a new table and inserts data | No |
Use INSERT INTO SELECT when the target schema is already defined.
4. Partial Column Insert Example
INSERT INTO products_archive (id, name)
SELECT id, name FROM products;
Only copies selected columns (must match in number and type).
5. Considerations and Validations
- The target table must exist
- Column count and data types must match
- Use transactions when performing bulk operations
Best Practices
| Recommended | Avoid This |
|---|---|
| Always list columns explicitly | Using SELECT * in production inserts |
Use WHERE to limit copied data | Overwriting existing records mistakenly |
Test queries with SELECT first | Assuming all columns auto-map |
Summary – Recap & Next Steps
INSERT INTO SELECT is a versatile SQL pattern to migrate, backup, or sync data into existing tables. It works across queries, joins, or filtered datasets.
Key Takeaways:
- Requires a pre-existing target table
- Use column lists to prevent errors
- Ideal for backups, ETL, and conditional migrations
Real-World Relevance:
Used in archiving, denormalization, logging, data transformation, and partitioning tasks.
Next: Learn MERGE, UPDATE FROM, or conditional inserts using CASE.
FAQ – SQL INSERT INTO SELECT
What’s the purpose of INSERT INTO SELECT?
To copy data from a source table into an existing destination table.
Do column names need to match?
No. But the order and data types must match.
Can I use INSERT INTO SELECT with joins?
Yes. Use complex SELECTs with JOINs as needed.
Is INSERT INTO SELECT available in MySQL and PostgreSQL?
Yes. Supported by most RDBMS including MySQL, PostgreSQL, SQL Server, and Oracle.
Share Now :
