SQL SELECT INTO

What is SQL SELECT INTO?

Copies the result set of a SELECT query into a newly created table in a single step.

Welcome to the Galaxy, Guardian!
You'll be receiving a confirmation email

Follow us on twitter :)

Oops! Something went wrong while submitting the form.

Compatible dialects for SQL SELECT INTO: Supports: SQL Server, PostgreSQL, Sybase ASE, Snowflake (via CTAS equivalent). Not supported: MySQL, MariaDB, Oracle (table-creation form), SQLite.

SQL SELECT INTO Full Explanation

SELECT INTO combines table creation and data population. When executed, the database engine creates a new table with the specified name, defines columns matching the data types of the selected expressions, and immediately inserts the query result set. Existing tables with the same name cause an error unless the dialect supports IF NOT EXISTS. Constraints, indexes, triggers, and identity properties are not copied unless explicitly handled afterward. In SQL Server and PostgreSQL, SELECT INTO is optimized for large bulk loads and runs as a minimally logged operation inside simple recovery models. Oracle and MySQL do not support SELECT INTO for creating tables; they use CREATE TABLE AS SELECT (CTAS) instead. In PL-SQL, SELECT INTO assigns query results to variables, which is unrelated to the table-creating form discussed here.

SQL SELECT INTO Syntax

-- Basic form (SQL Server, PostgreSQL)
SELECT [TOP n | DISTINCT] column_list
INTO new_table_name
FROM source_table
[WHERE filter]
[JOIN ...]
[GROUP BY ...];

-- Place temporary table in tempdb (SQL Server)
SELECT *
INTO #temp_sales
FROM sales
WHERE order_date >= '2024-01-01';

SQL SELECT INTO Parameters

column_list (list) - One or more columns or expressions to copy.
new_table_name (identifier) - Name of the table to create. Can be a regular, temporary, or external table depending on prefix.
source_table (identifier) - Table or view supplying the data.
IN database_name (identifier) - (PostgreSQL only) Store the new table in another database.
WHERE / JOIN / GROUP BY (clauses) - Same behavior as in a normal SELECT.

Example Queries Using SQL SELECT INTO

-- 1. Create a backup of active users
SELECT *
INTO users_active_backup
FROM users
WHERE status = 'active';

-- 2. Materialize last month orders into a temp table
SELECT order_id, customer_id, total
INTO #recent_orders
FROM orders
WHERE order_date BETWEEN '2024-05-01' AND '2024-05-31';

-- 3. Aggregate and store summary
SELECT customer_id, COUNT(*) AS orders_2024
INTO yearly_order_summary
FROM orders
WHERE YEAR(order_date) = 2024
GROUP BY customer_id;

Expected Output Using SQL SELECT INTO

A new table appears with the specified name and contains the rows returned by the SELECT query
Subsequent statements can read from it immediately

Use Cases with SQL SELECT INTO

Quick backups of critical rows before running updates or deletes
Staging data for complex reporting pipelines
Replacing slow subqueries with precomputed temporary tables
Persisting aggregated results for repeated downstream joins

Common Mistakes with SQL SELECT INTO

Assuming indexes and constraints are copied – they are not
Running SELECT INTO when a table of the same name already exists, leading to errors
Using it in MySQL or Oracle expecting CTAS behavior
Forgetting that SELECT INTO inside PL-SQL assigns to variables, not tables

First Introduced In

Microsoft SQL Server 6.0 (1995)

Frequently Asked Questions

Does SELECT INTO overwrite an existing table?

No. The statement fails with an error if the target table name already exists. Drop or rename the table first.

How do I add indexes after using SELECT INTO?

Execute ALTER TABLE or CREATE INDEX statements on the new table once data is loaded.

Why is SELECT INTO disallowed in MySQL?

MySQL reserves SELECT INTO for variable assignment in stored programs. Use CREATE TABLE new_table AS SELECT ... instead.

Can I combine SELECT INTO with JOINs and GROUP BY?

Yes. Any valid SELECT clauses (JOIN, WHERE, GROUP BY, HAVING, ORDER BY) may appear. ORDER BY is ignored for the physical storage order.