SQL INTO

What does SQL INTO do?

Populates a table with data either by inserting rows or by creating a new table from a query result.

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 INTO: INSERT INTO: Supported by all major databases. SELECT INTO: Supported by SQL Server, PostgreSQL, Oracle (PL/SQL), IBM Db2, Snowflake. Not supported by MySQL or SQLite.

SQL INTO Full Explanation

The INTO keyword appears in two main contexts. In INSERT statements it specifies the target table that will receive new rows. In SELECT statements it can create a new table from the query result set (supported in SQL Server, PostgreSQL, and others). INSERT INTO adds data to an existing table, while SELECT INTO both defines and fills a new table in a single step. Because SELECT INTO implicitly creates the destination table, column definitions are derived from the result set’s data types. Transactional rules of the surrounding session apply: the operation can be committed or rolled back. Permissions are required on the source tables and on the target schema for table creation or modification. Some systems, notably MySQL and SQLite, do not support SELECT INTO; they use CREATE TABLE … AS instead. Using INTO within stored procedures can simplify ETL staging tables, but be mindful of tempdb usage and naming collisions. Always check that the destination table does not already exist, or use TEMPORARY keywords where supported to avoid runtime errors.

SQL INTO Syntax

--INSERT usage
INSERT INTO target_table (col1, col2, col3)
VALUES (val1, val2, val3);

--SELECT usage (table-creation form)
SELECT col1, col2
INTO new_table
FROM source_table
WHERE status = 'active';

SQL INTO Parameters

target_table (identifier) - Existing table that will receive rows when using INSERT INTO.
new_table (identifier) - Name of the table to be created when using SELECT INTO.
column_list (list) - Optional explicit list of columns in the destination table for INSERT INTO.
values_list (list) - Literal values or expressions to insert.
select_list (list) - Columns or expressions produced by the SELECT query.
source_table (identifier) - Table(s) queried to supply data.

Example Queries Using SQL INTO

--1. Insert a single row
INSERT INTO users (id, email, role)
VALUES (101, 'jane@example.com', 'admin');

--2. Bulk insert from another table
INSERT INTO current_users (id, email)
SELECT id, email
FROM archived_users
WHERE last_login >= CURRENT_DATE - INTERVAL '30 days';

--3. Create and populate a staging table in one step
SELECT order_id, customer_id, total
INTO staging_large_orders
FROM orders
WHERE total > 10000;

Expected Output Using SQL INTO

The users table gains one new row.
current_users receives all qualifying rows; row count equals matching records in archived_users.
A new table called staging_large_orders is created and filled with high-value orders.

Use Cases with SQL INTO

Seeding reference data with INSERT INTO.
Copying data between tables during migrations.
Creating ad-hoc temporary tables for analysis via SELECT INTO.
Building ETL staging tables inside stored procedures.
Snapshotting data at a point in time without defining schemas manually.

Common Mistakes with SQL INTO

Forgetting column list in INSERT INTO when table has more columns than provided values.
Assuming SELECT INTO works in MySQL or SQLite; use CREATE TABLE AS instead.
Running SELECT INTO when the destination table already exists, causing errors.
Ignoring datatype changes that occur when SELECT expressions cast or aggregate data.
Omitting transactional control, leading to partial inserts on failure.

First Introduced In

SQL-92 (INSERT INTO). SELECT INTO popularized in early Sybase/SQL Server and adopted by PostgreSQL.

Frequently Asked Questions

What is the difference between INSERT INTO and SELECT INTO?

INSERT INTO appends rows to an existing table, while SELECT INTO creates a new table and fills it in one step.

Does SELECT INTO overwrite an existing table?

No. The statement fails if the target table name already exists in most systems. Use INSERT INTO or DROP TABLE first if you need to replace it.

Is SELECT INTO ANSI standard SQL?

Not entirely. Although INSERT INTO is standardized, SELECT INTO is proprietary to several dialects. For portable code use CREATE TABLE ... AS SELECT instead.

How can I speed up large INSERT INTO operations?

Batch the inserts, disable non-essential indexes or constraints temporarily, and use bulk-load utilities where available.