CTAS & Temporary Tables

A view re-runs its query every time. If that query takes 2 minutes and 50 people open the dashboard, that is 100 minutes of work. Sometimes you want to save the result once as a real table. That is what CTAS and temporary tables do.

Table types — where does the data live and for how long?

Definition — CTAS

CTAS (Create Table As Select) creates a new physical table and fills it with the rows returned by a SELECT — in one statement. The new table's columns and data types are copied from the query's result.

• New physical table — real rows on disk, just like any other table.
• One statement — no separate CREATE TABLE column list and INSERT.
• A snapshot — it stores a copy of the data at that moment. It does not change when the source table changes later. To refresh it, drop and re-create it.

Syntax

-- Standard / MySQL / PostgreSQL / SQLite
CREATE TABLE new_table AS
SELECT column_list
FROM source_table
WHERE condition;

-- SQL Server (T-SQL) — SELECT … INTO
SELECT column_list
INTO new_table
FROM source_table
WHERE condition;

Example — top batters snapshot (SQL Server)

SELECT id, name, runs
INTO top_batters
FROM players
WHERE runs >= 700;

SELECT * FROM top_batters;

The playground does not support SELECT … INTO, but it supports the standard CREATE TABLE top_batters AS SELECT …, which does the same job — try it below.

Example — snapshot vs view

CREATE VIEW v_top AS SELECT id, name, runs FROM players WHERE runs >= 700;
CREATE TABLE top_copy AS SELECT id, name, runs FROM players WHERE runs >= 700;

UPDATE players SET runs = 750 WHERE name = 'MS Dhoni';

SELECT 'view' AS source, COUNT(*) AS row_count FROM v_top
UNION ALL
SELECT 'ctas table', COUNT(*) FROM top_copy;

The view sees Dhoni's new 750 runs; the CTAS table is a frozen copy and still has 3 rows.

CTAS vs CREATE TABLE + INSERT

Note: SQL Server's SELECT … INTO copies column names, data types, NULL-ability and the IDENTITY property, but not primary keys, indexes, foreign keys, defaults or check constraints.

CTAS use cases

Definition — temporary table

A temporary table is a real table that is visible only to the session (connection) that created it and is dropped automatically when that session ends. In SQL Server its name starts with # and it is stored in the system database tempdb.

Syntax — temporary tables

-- SQL Server: SELECT … INTO a #temp table
SELECT id, name, runs
INTO #temp_big
FROM players
WHERE runs >= 500;

-- SQL Server: or create it first, then insert
CREATE TABLE #temp_big (id INT, name VARCHAR(50), runs INT);
INSERT INTO #temp_big SELECT id, name, runs FROM players WHERE runs >= 500;

-- MySQL / PostgreSQL
CREATE TEMPORARY TABLE temp_big AS
SELECT id, name, runs FROM players WHERE runs >= 500;

Temp tables are great for breaking a long job into steps: step 1 saves a filtered set into #step1, step 2 reads #step1 and saves into #step2, and so on. Unlike a CTE, each step's result is actually stored, can be indexed, and can be queried many times in the same session.

Common mistakes

• ❌ Running SELECT … INTO top_batters twice → error "There is already an object named 'top_batters'". ✅ DROP TABLE IF EXISTS top_batters; first (SQL Server 2016+).
• ❌ Expecting a CTAS table to update itself. ✅ it is a snapshot — re-create it, or use a view for live data.
• ❌ Expecting another user to see your #temp table. ✅ local temp tables are private to your session.
• ❌ Writing CREATE TABLE … AS SELECT in SQL Server → syntax error. ✅ SQL Server uses SELECT … INTO.

SQL Server vs MySQL / PostgreSQL

Interview tip

Q: Temp table vs CTE? A CTE is just a named query for one statement — nothing is stored. A temp table physically stores rows for your whole session, can be indexed and reused by many statements. Use a temp table when an intermediate result is large or used several times.