Create a Temporary Table

Build a table that exists only for the current session and is discarded automatically when the connection closes.

5/5 supported engines validation-green 1 example 2 scenarios

Docker-validated Not currently validation-green

Example 1

Stage high-value orders in a temp table then query it

Only orders with total >= 100 (Alice: 150, Carol: 220) pass the filter and land in the temp table. Bob (40) and Dave (90) are excluded. The second SELECT then queries the staged subset. The temp table is automatically dropped when the session ends — there is no need to explicitly DROP it, though doing so early is good practice when the result is large.

MySQL MariaDB SQLite

Engine-specific syntax

Source table data

Setup

CREATE TABLE orders (id INT, customer VARCHAR(50), total INT);

INSERT INTO
  orders
VALUES
  (1, 'Alice', 150),
  (2, 'Bob', 40),
  (3, 'Carol', 220),
  (4, 'Dave', 90);

Validated query

SQL

CREATE TEMPORARY TABLE high_value AS
SELECT
  id,
  customer,
  total
FROM
  orders
WHERE
  total >= 100;

SELECT
  id,
  customer,
  total
FROM
  high_value
ORDER BY
  total DESC;

Expected result

id	customer	total
3	Carol	220
1	Alice	150

SQL Server

Engine-specific syntax

Source table data

Setup

CREATE TABLE orders (id INT, customer VARCHAR(50), total INT);

INSERT INTO
  orders
VALUES
  (1, 'Alice', 150),
  (2, 'Bob', 40),
  (3, 'Carol', 220),
  (4, 'Dave', 90);

Validated query

SQL

SELECT id, customer, total INTO #high_value FROM orders WHERE total >= 100; SELECT id, customer, total FROM #high_value ORDER BY total DESC;

Expected result

id	customer	total
3	Carol	220
1	Alice	150

PostgreSQL

Engine-specific syntax

Source table data

Setup

CREATE TABLE orders (id INT, customer VARCHAR(50), total INT);

INSERT INTO
  orders
VALUES
  (1, 'Alice', 150),
  (2, 'Bob', 40),
  (3, 'Carol', 220),
  (4, 'Dave', 90);

Validated query

SQL

CREATE TEMP TABLE high_value AS
SELECT
  id,
  customer,
  total
FROM
  orders
WHERE
  total >= 100;

SELECT
  id,
  customer,
  total
FROM
  high_value
ORDER BY
  total DESC;

Expected result

id	customer	total
3	Carol	220
1	Alice	150

SQL Server uses SELECT … INTO #high_value. All other engines use CREATE TEMPORARY/TEMP TABLE … AS SELECT. Results are identical.

Useful when

Where this command helps.

staging a filtered or aggregated subset before joining it to other tables
materialising a recursive CTE result to avoid re-evaluating it in multiple queries

Explanation

What the command is doing.

Temporary tables are useful for staging intermediate results, breaking apart a complex query, or materialising an expensive subquery that is referenced multiple times. MySQL, MariaDB, and SQLite use CREATE TEMPORARY TABLE. PostgreSQL accepts both CREATE TEMPORARY TABLE and the shorter CREATE TEMP TABLE. SQL Server names temp tables with a # prefix inside a regular CREATE TABLE statement — the # signals that it lives in tempdb and is session-scoped. All engines also support CREATE … AS SELECT to populate the temp table from a query in a single step, except SQL Server, which uses SELECT … INTO #table.

Create a Temporary Table

Stage high-value orders in a temp table then query it

Where this command helps.

What the command is doing.

Keep moving through nearby tasks.