SQL SELECT INTO – Copy Data into a New Table
Introduction – What is SQL SELECT INTO?
The SELECT INTO statement in SQL is used to create a new table and insert data into it from an existing query result. It is a convenient way to duplicate or export data without defining the new table structure manually.
In this guide, you’ll learn:
- Syntax and usage of
SELECT INTO - Differences from
INSERT INTO SELECT - Real-world scenarios and performance considerations
1. Basic SELECT INTO Syntax
SELECT column1, column2
INTO new_table
FROM existing_table
WHERE condition;
Creates new_table and populates it with selected rows and columns.
2. Example – Archive Old Orders
SELECT *
INTO archived_orders
FROM orders
WHERE order_date < '2023-01-01';
Creates a new table archived_orders with only historical data.
3. SELECT INTO vs INSERT INTO SELECT
| Operation | Description | Table Required Beforehand |
|---|---|---|
SELECT INTO | Creates and fills a new table | |
INSERT INTO SELECT | Inserts rows into an existing table |
Use SELECT INTO for one-time snapshots or exports.
4. SELECT INTO with Joins or Functions
SELECT e.name, d.name AS dept
INTO employee_summary
FROM employees e
JOIN departments d ON e.dept_id = d.id;
Useful for summarizing across relationships.
5. Limitations and Considerations
- Not all database systems support
SELECT INTO(e.g., MySQL prefersCREATE TABLE + INSERT) - Indexes, constraints, and triggers are not copied
- Avoid using it in production without proper naming/backup
Best Practices
| Recommended | Avoid This |
|---|---|
| Use for staging or temporary snapshots | Assuming it copies indexes or constraints |
Name output tables clearly (_copy, _temp) | Using in live apps without testing |
Use WHERE clause to limit rows | Copying full tables unnecessarily |
Summary – Recap & Next Steps
SELECT INTO is a fast and simple way to copy data into a new table, perfect for backups, exports, or data reshaping. It’s efficient but not meant for transactional workflows.
Key Takeaways:
SELECT INTOcreates and fills a new table- Ideal for snapshots, backups, exports, or development usage
- Doesn’t copy indexes, keys, or triggers
Real-World Relevance:
Used in ETL pipelines, reporting layers, data science prep, and audit snapshots.
FAQ – SQL SELECT INTO
What does SELECT INTO do?
It creates a new table and fills it with data from a SELECT query.
Can SELECT INTO be used with joins?
Yes. You can build the new table using any complex SELECT query.
Does SELECT INTO copy constraints and indexes?
No. Only structure (columns and types) and data are copied.
Can I use SELECT INTO in MySQL?
No native support. Use CREATE TABLE + INSERT INTO SELECT instead.
Share Now :
