SQL Select Into

The SELECT INTO statement copies data from one table and inserts it into a new table. It is primarily used to create table backups, archive historical data, or generate temporary working tables for complex data transformations.

Syntax & Engine Support

Important: Support for SELECT INTO varies across database engines.

1. SQL Server / MS Access / PostgreSQL Syntax

In databases that support SELECT INTO, the target table is created automatically during execution based on the structure and data types of the source columns:

SELECT column1, column2, column3, ...
INTO new_table [IN external_db]
FROM existing_table
WHERE condition;

2. MySQL / Oracle Equivalent (CREATE TABLE AS SELECT)

MySQL and standard Oracle do not support the SELECT INTO syntax for creating new tables. Instead, they use the ANSI-standard CREATE TABLE ... AS SELECT (often called CTAS):

CREATE TABLE new_table AS
SELECT column1, column2, column3, ...
FROM existing_table
WHERE condition;

Key Use Cases & Examples

1. Creating a Complete Table Backup

To copy all columns and rows from an existing Customers table into a new Customers_Backup table:

-- SQL Server / MS Access / PostgreSQL
SELECT * 
INTO Customers_Backup 
FROM Customers;

-- MySQL / Oracle / PostgreSQL (Alternative)
CREATE TABLE Customers_Backup AS 
SELECT * FROM Customers;

2. Copying Selected Columns

You can limit the copy to specific columns to create lightweight summary tables:

SELECT CustomerID, CustomerName, Phone
INTO CustomerContacts
FROM Customers;

3. Copying Filtered Subsets of Data

Combine SELECT INTO with a WHERE clause to extract specific subsets of data—such as archiving older records or segmenting by region:

SELECT *
INTO GermanCustomers
FROM Customers
WHERE Country = 'Germany';

4. Joining Multiple Tables into a New Table

You can join multiple tables together and save the consolidated output into a standalone table:

SELECT 
    o.OrderID, 
    c.CustomerName, 
    s.ShipperName, 
    o.OrderDate
INTO OrderSummary_2026
FROM Orders o
INNER JOIN Customers c ON o.CustomerID = c.CustomerID
INNER JOIN Shippers s ON o.ShipperID = s.ShipperID
WHERE o.OrderDate >= '2026-01-01';

5. Creating an Empty Table Schema (No Data)

To copy only the structure (column names and data types) of a table without copying any actual rows, add a WHERE condition that evaluates to FALSE:

SELECT *
INTO Empty_Customers_Template
FROM Customers
WHERE 1 = 0;

Critical Considerations

• Primary Keys & Indexes: SELECT INTO copies the columns, default values, and data types, but it does not copy primary keys, foreign keys, indexes, or triggers from the original table. You must add constraints and indexes manually to the newly created table.
• Target Table Existence: The target table named after INTO must not already exist. If the table exists, the query will throw an error. (To append data into an existing table, use INSERT INTO ... SELECT).
• Permissions: Executing a SELECT INTO statement requires CREATE TABLE permissions on the database, in addition to SELECT permissions on the source table.