SQL SELECT INTO

SELECT INTO copies the results of a query into a brand-new table, created automatically with columns matching the SELECT list. It's also one of the biggest syntax splits in this entire course, SELECT ... INTO is genuinely SQL Server syntax, every other database in this course uses a different statement entirely for the same idea.

Learning Objectives

Use SELECT ... INTO on SQL Server to create a new table from query results.
Use CREATE TABLE ... AS SELECT on every other database in this course for the same result.
Recognize Oracle's own SELECT INTO as a different, unrelated concept.

SQL Server: SELECT ... INTO

high_earners is created fresh, with columns and types inferred from the SELECT list, already populated with every matching row. If a table named high_earners already exists, this errors, SELECT INTO only ever creates a new table.

SQL Server

SELECT first_name, salary
INTO high_earners
FROM employees
WHERE salary > 90000;

Every Other Database: CREATE TABLE AS SELECT

Same outcome as SQL Server's version, a new table, same columns as the SELECT list, already populated, just a completely different keyword arrangement. This pattern is often called CTAS, Create Table As Select, in documentation and in conversation.

MySQL / MariaDB / PostgreSQL / SQLite / Oracle

CREATE TABLE high_earners AS
SELECT first_name, salary
FROM employees
WHERE salary > 90000;

Syntax by Database

Database	Syntax to Create a Table from a Query
SQL Server	SELECT ... INTO new_table FROM ...
MySQL / MariaDB / PostgreSQL / SQLite / Oracle	CREATE TABLE new_table AS SELECT ...

NOTE

Oracle also has its own SELECT INTO, but it means something different entirely: inside PL/SQL code, it assigns a single row's values into variables, not a table-creation statement at all. Don't confuse the two just because the keywords match, Oracle's table-from-query equivalent is CREATE TABLE AS SELECT, same as MySQL, MariaDB, PostgreSQL, and SQLite.

Try It Yourself

Write the statement that creates a new table, engineering_team, containing every Engineering department employee's first_name and salary, for both SQL Server and PostgreSQL.

Answer — SQL Server

SELECT first_name, salary
INTO engineering_team
FROM employees
WHERE department_id = 1;

Answer — PostgreSQL

CREATE TABLE engineering_team AS
SELECT first_name, salary
FROM employees
WHERE department_id = 1;

Frequently Asked Questions

Does the new table keep constraints like PRIMARY KEY from the source table?

No, neither SELECT INTO nor CREATE TABLE AS SELECT copies constraints, indexes, or defaults, only column names, inferred types, and the actual row data. Constraints need to be added afterward with ALTER TABLE, covered in Module 8.

Can SELECT INTO create an empty table, with no rows?

Yes, add a WHERE condition that matches nothing (WHERE 1 = 0 is a common idiom) to create a table with the right columns and types but zero rows, a quick way to clone a table's structure alone.

Common Beginner Mistakes

Using SELECT ... INTO syntax on MySQL or PostgreSQL

This fails outside SQL Server, those databases don't recognize that syntax for table creation at all. Use CREATE TABLE ... AS SELECT instead.

Running SELECT INTO against a table name that already exists

It errors, both SELECT INTO and CREATE TABLE AS SELECT only create brand-new tables. Inserting into an already-existing table is INSERT INTO SELECT instead, covered next.

Best Practices

Know which syntax applies before writing the query, this is one of the few places guessing based on "standard SQL" genuinely doesn't work.
Remember constraints and indexes don't carry over, add them explicitly afterward if the new table needs them.
Use a clearly temporary or snapshot-style name for tables created this way, to avoid confusing them with permanently designed tables.

Interview Questions

Why doesn't SELECT ... INTO work the same way on every database?

SELECT INTO is SQL Server-specific syntax for creating a table from a query. Every other database in this course (MySQL, MariaDB, PostgreSQL, SQLite, Oracle) uses CREATE TABLE ... AS SELECT for the identical outcome, a genuine, non-portable syntax split rather than a minor keyword difference.

Does a table created with CREATE TABLE AS SELECT keep the source table's primary key?

No. Only column names, inferred data types, and the selected rows carry over, constraints like PRIMARY KEY, FOREIGN KEY, indexes, and defaults do not. They'd need to be added afterward with ALTER TABLE.

Summary

SELECT INTO creates a brand-new table from a query's results, SQL Server's own syntax for it, while every other database in this course uses CREATE TABLE ... AS SELECT instead, the same idea under a completely different keyword arrangement. Neither copies constraints or indexes from the source table.

What's Next?

INSERT INTO SELECT next, copying query results into an already-existing table, this time fully portable across every database in this course.

</>

Ready to Practice SQL?

Write and run real SQL queries online, right in your browser, no install or signup required. Pick a playground based on which database you want to practice on.

SQLite / PostgreSQL

Fast, no server, no ads.

Open DB Fiddle ↗

MySQL / SQL Server

Switch databases with one dropdown.

Open SQL Fiddle ↗

Both open a third-party SQL playground in a new tab. External site terms and privacy policy apply.