SQL guide · Querying

SQL SELECT INTO: Create a New Table From a Query

The SELECT INTO statement runs a query and stores its result in a brand new table. The columns of the new table take their names and data types from the select list, and the rows come from the query. It is a quick way to make a backup copy of a table, build a staging table for a data load, or save an expensive intermediate result so that later queries can reuse it without recomputing.

This form of SELECT INTO is specific to SQL Server and a few related databases such as Sybase and MS Access. Other databases achieve the same result with CREATE TABLE ... AS SELECT, usually shortened to CTAS. The outcome is similar, but the details differ. In particular, the new table normally does not inherit constraints, indexes, defaults or triggers from the source table. You get the column names, types and data, and you must add primary keys and indexes afterward with ALTER TABLE and CREATE INDEX.

The statement fails if the target table already exists, so scripts that run repeatedly need to drop the table first or check for it. Adding a WHERE clause that can never be true, such as WHERE 1 = 0, copies only the structure and no rows, which is a well known trick for cloning a table definition. Be aware that in PostgreSQL and MySQL the keyword INTO has other meanings, for example inside PL/pgSQL where SELECT ... INTO variable assigns values to variables rather than creating a table.

Basic syntax

-- SQL Server, MS Access
SELECT column1, column2
INTO new_table
FROM source_table
[WHERE condition];

-- PostgreSQL, MySQL, SQLite, Oracle
CREATE TABLE new_table AS
SELECT column1, column2
FROM source_table
[WHERE condition];

new_table must not exist yet. Column names in the new table come from the select list, so give calculated columns an alias. Add IN external_database (MS Access) or use a three-part name (SQL Server) to create the table in another database.

SQL SELECT INTO examples

Back up a table

This creates a full copy of the customers table with its current data. Constraints and indexes are not copied, so the backup is a plain heap of rows.

SELECT *
INTO customers_backup
FROM customers;

Copy a subset of columns and rows

Select only what you need and filter rows to build a smaller working table. The alias gives the calculated column a proper name in the new table.

SELECT customer_id,
       first_name,
       email,
       UPPER(country) AS country_code
INTO eu_customers
FROM customers
WHERE country IN ('DE', 'FR', 'ES');

Copy only the structure

A condition that is always false returns zero rows, so the new table has the same columns and types as the source but is empty. It is a quick way to make a template table.

SELECT *
INTO orders_template
FROM orders
WHERE 1 = 0;

The CREATE TABLE AS equivalent

PostgreSQL, MySQL, SQLite and Oracle use CREATE TABLE AS. This builds a summary table of monthly revenue that dashboards can read quickly.

CREATE TABLE monthly_revenue AS
SELECT EXTRACT(YEAR FROM order_date)  AS order_year,
       EXTRACT(MONTH FROM order_date) AS order_month,
       SUM(total_amount)              AS revenue
FROM orders
GROUP BY EXTRACT(YEAR FROM order_date), EXTRACT(MONTH FROM order_date);

Where SQL dialects differ

SQL Server: Supports SELECT ... INTO new_table directly, including #temp tables for session-scoped copies. The identity property carries over from a single source column, which can surprise you; use CAST or IDENTITY() to control it. CREATE TABLE AS is not supported.

PostgreSQL: Prefer CREATE TABLE new_table AS SELECT .... PostgreSQL still accepts SELECT ... INTO new_table at the top level, but the documentation recommends CTAS. Add WITH NO DATA to copy only the structure. CREATE TEMP TABLE ... AS makes a session table.

MySQL: Uses CREATE TABLE new_table AS SELECT ... or CREATE TABLE ... SELECT. SELECT ... INTO in MySQL writes to variables, OUTFILE or DUMPFILE, not to a table. Use CREATE TABLE new_table LIKE old_table to clone the structure with indexes.

SQLite: Uses CREATE TABLE new_table AS SELECT .... The column types are derived from the expression affinity, so computed columns may end up with no declared type. CREATE TEMP TABLE ... AS creates a temporary table.

Oracle: Uses CREATE TABLE new_table AS SELECT ..., and WHERE 1 = 0 copies the structure only. In PL/SQL, SELECT ... INTO variable fetches a single row into variables and raises an error if zero or several rows return.

Common mistakes

  • Running the statement twice. The second run fails because the target table already exists, so drop it first or test for it.
  • Expecting primary keys, indexes, defaults and triggers to be copied. Only the columns and data come across; recreate the rest.
  • Writing SELECT ... INTO in MySQL or Oracle SQL and expecting a new table. Those databases need CREATE TABLE AS.
  • Forgetting aliases on expressions, which leaves the new table with unnamed or duplicated columns and often an error.
  • Copying huge tables without a filter in production, which can lock the source and fill the transaction log or disk.

Related SQL guides

Browse all SQL guides

Need help writing the SQL?

Describe your tables and the result you want, then review the generated query against your database before running it.

Try the AI SQL generator