SELECT INTO has dialect-specific meanings: SQL Server can create a table from a query, while other contexts use it to assign variables.
SELECT INTO is a particularly misleading phrase to copy between tutorials. The same words can create a table, assign a program variable or be unsupported. Identify the database and execution context before choosing this form.
This reference adds detail to the main course. You can read it independently: the small database below supplies every table and row used in the examples. Start with the question each query answers, then compare the returned values with the data rather than memorising a line of syntax.
Set up the practice database
Run this setup once in a new practice database, before the examples. It uses four customers and six sales. Ana has two sales, Ben has one, Cara has two, and Dev has none.
Sale 106 has no customer link; sale 104 has an unknown amount. These deliberate test cases expose missing information and unmatched relationships.
CREATE TABLE customers (
id INTEGER PRIMARY KEY,
name VARCHAR(40) NOT NULL,
city VARCHAR(40),
manager_id INTEGER
);
CREATE TABLE sales (
id INTEGER PRIMARY KEY,
customer_id INTEGER,
amount DECIMAL(10,2),
status VARCHAR(20) NOT NULL,
created_on DATE NOT NULL
);
INSERT INTO customers (id,name,city,manager_id) VALUES
(1,'Ana','Leeds',NULL),(2,'Ben','Leeds',1),
(3,'Cara','Porto',1),(4,'Dev',NULL,2);
INSERT INTO sales (id,customer_id,amount,status,created_on) VALUES
(101,1,10,'paid','2026-01-01'),
(102,1,30,'pending','2026-01-02'),
(103,2,20,'paid','2026-01-02'),
(104,3,NULL,'pending','2026-02-01'),
(105,3,20,'paid','2026-02-02'),
(106,NULL,5,'paid','2026-02-03');An amount of zero would be a known value. The NULL amount here means the value is unknown. A customer with no matching sale differs from a sale whose customer_id is NULL.
The primary keys identify individual records even when amounts or cities repeat. Dates use year-month-day format. The examples specify sorting whenever order matters; insertion order alone does not guarantee display order.
How SELECT INTO works
In SQL Server, SELECT columns INTO new_table FROM source creates a new table and fills it with the selected rows. The table must not already exist. Its column names and inferred types come from the selected expressions. Add useful aliases for calculated expressions so the created table has clear field names. The operation combines table creation with data copying, unlike INSERT INTO SELECT which writes into an existing target definition.
The inferred table is not a complete clone of the source schema. Indexes, constraints and triggers are not generally carried over automatically in SQL Server SELECT INTO. Identity inheritance has documented exceptions, including particular joins and expressions. If the destination needs a controlled primary key, foreign keys or exact column definitions, create it explicitly and then use INSERT INTO SELECT. That approach makes the intended schema visible and easier to review.
What SELECT INTO means in each database
PostgreSQL supports a SQL SELECT INTO table form historically, but recommends CREATE TABLE AS for new applications. In PostgreSQL procedural PL/pgSQL, SELECT INTO assigns query values to variables rather than creating a table. MySQL SELECT ... INTO is used for variables or output destinations such as files; table creation uses CREATE TABLE ... AS SELECT. SQLite also uses CREATE TABLE ... AS SELECT and does not provide the same SELECT INTO table statement.
Create-as-query statements infer result structure rather than migrating every business rule. Treat them as useful tools for snapshots, staging and disposable reporting data. A created snapshot does not automatically refresh when the source changes. Review permissions, target name collisions and transaction behaviour. In SQL Server, a failed insert phase can leave an empty created table unless the operation is protected by the appropriate transaction; do not assume every failure removes the target object automatically.
MySQL, PostgreSQL, SQL Server and SQLite
The demonstrated table-creation form is SQL Server-specific in this lesson. PostgreSQL supports a related SQL form but prefers CREATE TABLE AS; PL/pgSQL INTO assigns variables. MySQL and SQLite use CREATE TABLE snapshot AS SELECT ... for this task. MySQL SELECT INTO variables or OUTFILE is a different operation and should not be presented as creating a table.
Check the engine-specific syntax and version requirements in SQL Server SELECT INTO, PostgreSQL CREATE TABLE AS.
Practice task and worked solution
In PostgreSQL, MySQL or SQLite, create the same paid snapshot using CREATE TABLE AS and verify its rows. Use a distinct name from the T-SQL example.
Try the task before reading its worked solution. The supplied rows make the expected answer checkable by hand.
CREATE TABLE paid_snapshot_as AS
SELECT id AS sale_id,amount AS total FROM sales WHERE status='paid';
SELECT sale_id,total FROM paid_snapshot_as ORDER BY sale_id;sale_id | total
101 | 10
103 | 20
105 | 20
106 | 5The query result becomes a new table in the three listed engines. This worked alternative is not ordinary SQL Server CREATE TABLE syntax. It produces the same chosen values while still leaving schema constraints and indexes to be added explicitly if needed.
After creating the snapshot, change a source amount in a disposable copy and confirm the snapshot does not update automatically. Inspect the new table’s constraints to verify that copying values did not recreate the source primary key. Use CREATE TABLE AS for PostgreSQL, MySQL or SQLite, keeping the SQL Server SELECT INTO experiment clearly separate from variable-assignment contexts.
SQL SELECT INTO: New Tables vs Variables by Dialect Syntax
SELECT INTO
-- SQL Server / T-SQL example.
SELECT id AS sale_id,amount AS total
INTO paid_snapshot
FROM sales WHERE status='paid';
SELECT sale_id,total FROM paid_snapshot ORDER BY sale_id;SQL SELECT INTO: New Tables vs Variables by Dialect Summary
- In SQL Server,
SELECT ... INTO new_table FROM ...creates a new table and fills it with the query's rows. - PostgreSQL, MySQL and SQLite use
CREATE TABLE new_table AS SELECT ...for the same job. - Inside procedures,
SELECT ... INTOassigns values to variables, a completely different meaning. - The new table copies column names and types, but not primary keys, indexes or other constraints.
- The copy is a snapshot: later changes to the source table don't appear in it.
SQL SELECT INTO: New Tables vs Variables by Dialect Examples
Each example shows the code, the output it produces and what each part does.
A paid-sales snapshot in SQL Server
This T-SQL statement creates paid_snapshot from four selected paid sales. The second statement verifies its contents. The new column labels come from the SELECT aliases; the source primary key is not automatically reproduced as a primary-key constraint.
-- SQL Server / T-SQL example.
SELECT id AS sale_id,amount AS total
INTO paid_snapshot
FROM sales WHERE status='paid';
SELECT sale_id,total FROM paid_snapshot ORDER BY sale_id;sale_id | total
101 | 10
103 | 20
105 | 20
106 | 5Common SQL SELECT INTO: New Tables vs Variables by Dialect Mistakes
Expecting keys and indexes to be copied
Assuming SELECT INTO copies indexes or constraints can leave a reporting table without its intended uniqueness guarantees. Assuming it creates a table in a MySQL stored program can instead invoke variable assignment syntax. State the engine and context, and define required constraints separately.
SQL SELECT INTO: New Tables vs Variables by Dialect in Real Projects
Disposable snapshots for analysis
An analyst can create a disposable snapshot for a one-off investigation. Include the snapshot date in its operational description and remember it is independent of later source changes. For an application’s permanent reporting schema, use explicit definitions and controlled migrations rather than relying on inferred fields.
Use create-as-query snapshots for a named investigation rather than assuming they are complete application migrations. Inspect inferred column types and add any required keys deliberately. A snapshot has a creation time and stale-data risk; if consumers need live results, a view or a refreshed reporting process may fit their requirements better.
SQL SELECT INTO: New Tables vs Variables by Dialect FAQ
Does SELECT INTO mean table creation everywhere?
No. Its meaning changes by database and procedural context: SQL Server creates a table, while PL/pgSQL and MySQL stored programs assign query results to variables. State which engine a query is for when sharing it.
Does the new table stay synchronised?
No. A copied snapshot does not refresh itself; it holds the rows as they were when the statement ran. Use a view for an always-current result, or a materialised view or scheduled job for a stored copy that's refreshed.
When should I prefer INSERT INTO SELECT?
When the target already exists or needs a deliberately defined schema with constraints, keys and indexes. Create the table with a full CREATE TABLE first, then fill it with INSERT INTO ... SELECT, as the INSERT INTO SELECT lesson shows.
How do I copy only a table's structure?
Select no rows: CREATE TABLE empty_copy AS SELECT * FROM sales WHERE 1 = 0;, or SELECT * INTO empty_copy FROM sales WHERE 1 = 0; in SQL Server. This copies column names and types only; add keys and constraints yourself.
Can SELECT INTO create a temporary table?
Yes. In SQL Server, SELECT ... INTO #temp_sales FROM sales creates a temporary table that disappears at the end of the session. PostgreSQL, MySQL and SQLite use CREATE TEMPORARY TABLE ... AS SELECT ..., covered in the temporary tables lesson.
SQL SELECT INTO: New Tables vs Variables by Dialect Exercises
Answer each one and check it instantly. If you get stuck, open the hint before the answer.
What row count appears in the paid snapshot made from the fixture?
SQL SELECT INTO: New Tables vs Variables by Dialect Quiz
2 questions to check what you learned.
What does SQL Server SELECT INTO new_table do?
- Creates the new table and inserts the selected rows
- Appends selected rows to an already existing table without defining it
- Assigns a client variable and never creates a table
What is the table-creation alternative in SQLite?
- INSERT INTO a missing table without defining it
- CREATE TABLE ... AS SELECT ...
- SELECT ... INTO new_table, using the T-SQL form unchanged