SQL Temp Table Guide

July 12, 2025 By Matthew Rathbone

A temp table is a table that only lasts as long as your database session. You fill it with rows, query it like any other table, and the database drops it for you when you disconnect. The examples work in PostgreSQL, MySQL, SQLite and SQL Server.

How to use SQL temp tables

Create a temp table like a normal table, with the TEMPORARY keyword. Most often you fill it straight from a query:

CREATE TEMPORARY TABLE table_name AS
SELECT ...;

SQL Server is the exception: a # at the start of the name makes a table temporary, and SELECT ... INTO creates and fills it.

-- Input: orders
-- id | customer | total | status
-- ---+----------+-------+---------
-- 1  | Maria    | 120   | paid
-- 2  | Tom      | 35    | refunded
-- 3  | Priya    | 89    | paid

CREATE TEMPORARY TABLE paid_orders AS
SELECT id, customer, total FROM orders WHERE status = 'paid';
-- SQL Server: SELECT id, customer, total INTO #paid_orders FROM orders WHERE status = 'paid';

SELECT * FROM paid_orders;  -- #paid_orders in SQL Server

-- Output:
-- id | customer | total
-- ---+----------+------
-- 1  | Maria    | 120
-- 3  | Priya    | 89

You can also define the columns first and insert rows later, as with a permanent table:

CREATE TEMPORARY TABLE temp_students (  -- SQL Server: CREATE TABLE #temp_students (
  student_id INT PRIMARY KEY,
  first_name VARCHAR(50),
  last_name VARCHAR(50),
  age INT
);

INSERT INTO temp_students (student_id, first_name, last_name, age)
VALUES (1, 'John', 'Doe', 20);

SELECT * FROM temp_students;

-- Output:
-- student_id | first_name | last_name | age
-- -----------+------------+-----------+----
-- 1          | John       | Doe       | 20

Rules to know

Only your session can see it. Other connections can’t read it, and the database drops it when your session ends. If your application uses a connection pool, create the temp table and query it on the same connection.
It hides a permanent table with the same name. Once you create a temp table called orders, every query on orders reads the temp table until you drop it, so pick names that can’t clash. SQL Server’s # prefix avoids this.
Creating it twice fails. Run the same script twice in one session and the CREATE fails because the table already exists. Drop it first:

DROP TABLE IF EXISTS paid_orders;
-- In MySQL, DROP TEMPORARY TABLE IF EXISTS can never drop a permanent table by mistake

MySQL can’t read a temp table twice in one query. A self-join, or a subquery on the same temp table, fails with ERROR 1137 (HY000): Can't reopen table. Make a second copy if you need one.

Alternative syntax

CREATE TEMP TABLE is a shorter form that PostgreSQL and SQLite accept (the exercises below use it). MySQL doesn’t.
SQL Server global temp tables start with two hashes, ##paid_orders. Every session can read one, and SQL Server drops it when the session that created it ends.
A CTE does the same job when only one query needs the rows. Nothing is stored, so there’s nothing to drop:

-- Input: the orders table above

WITH paid_orders AS (
  SELECT id, customer, total FROM orders WHERE status = 'paid'
)
SELECT * FROM paid_orders;

-- Output:
-- id | customer | total
-- ---+----------+------
-- 1  | Maria    | 120
-- 3  | Priya    | 89

When should you use a temp table?

Use a temp table when you want a result to stick around for more than one query, but not for longer than your session.

Reuse the same rows in several queries

When several reports start from the same filtered rows, such as this month’s orders or the active projects, save them once and query the temp table instead of repeating the filter each time.

Store totals you want to filter or reuse

Aggregate once with GROUP BY and keep the totals in a temp table. You can then filter them, rank them or compare them against their own average without recalculating.

Break a complex query into steps

A query with several layers of subqueries is hard to read and debug. Build each step as a temp table, check its rows, then join it to your permanent tables in the next step.

Try out changes on a copy

Before running an UPDATE or DELETE on real data, copy the table into a temp table and run it there. You can see what changes, and the original stays untouched.

Real examples of SQL temp tables

In SQL Server, create each of these temp tables with SELECT ... INTO #name instead of CREATE TEMPORARY TABLE.

Report on one month’s orders twice

Filter October’s orders into a temp table once, then run as many reports on it as you like:

-- Input: orders
-- id | customer | order_date | total
-- ---+----------+------------+------
-- 1  | Maria    | 2026-09-28 | 120
-- 2  | Tom      | 2026-10-02 | 35
-- 3  | Maria    | 2026-10-05 | 60
-- 4  | Priya    | 2026-10-09 | 89

CREATE TEMPORARY TABLE october_orders AS
SELECT id, customer, total
FROM orders
WHERE order_date >= '2026-10-01' AND order_date < '2026-11-01';

SELECT COUNT(*) AS order_count, SUM(total) AS revenue
FROM october_orders;

-- Output:
-- order_count | revenue
-- ------------+--------
-- 3           | 184

SELECT customer, SUM(total) AS spent
FROM october_orders
GROUP BY customer
ORDER BY spent DESC;

-- Output:
-- customer | spent
-- ---------+------
-- Priya    | 89
-- Maria    | 60
-- Tom      | 35

Find customers who spend more than average

Store each customer’s total, then compare the totals with their own average:

-- Input: orders
-- id | customer | total
-- ---+----------+------
-- 1  | Maria    | 120
-- 2  | Tom      | 35
-- 3  | Maria    | 60
-- 4  | Priya    | 89
-- 5  | Priya    | 70

CREATE TEMPORARY TABLE customer_totals AS
SELECT customer, SUM(total) AS spent
FROM orders
GROUP BY customer;

SELECT customer, spent
FROM customer_totals
WHERE spent > (SELECT AVG(spent) FROM customer_totals)
ORDER BY spent DESC;

-- Output:
-- customer | spent
-- ---------+------
-- Maria    | 180
-- Priya    | 159

This one won’t run in MySQL, which can’t read customer_totals twice in one query (Can't reopen table). Work out the average in a separate query first.

Find customers who haven’t ordered lately

Work out each customer’s latest order in one step, then join that to the customer list. On big tables, an index on the temp table speeds up the join:

-- Input: customers
-- id | name
-- ---+------
-- 1  | Maria
-- 2  | Tom
-- 3  | Priya

-- Input: orders
-- id | customer_id | order_date
-- ---+-------------+-----------
-- 1  | 1           | 2026-09-30
-- 2  | 2           | 2026-05-14
-- 3  | 1           | 2026-04-02

-- Step 1: each customer's latest order
CREATE TEMPORARY TABLE last_orders AS
SELECT customer_id, MAX(order_date) AS last_order
FROM orders
GROUP BY customer_id;

-- Step 2: on big tables, index the column you'll join on
CREATE INDEX idx_last_orders ON last_orders (customer_id);

-- Step 3: customers with no order since July 1
SELECT c.name, l.last_order
FROM customers c
LEFT JOIN last_orders l ON l.customer_id = c.id
WHERE l.last_order IS NULL OR l.last_order < '2026-07-01'
ORDER BY c.name;

-- Output:
-- name  | last_order
-- ------+-----------
-- Priya | NULL
-- Tom   | 2026-05-14

Check what a DELETE would remove

Clearing out old cancelled orders? Run the DELETE on a copy first and look at what’s left:

-- Input: orders
-- id | customer | status    | order_date
-- ---+----------+-----------+-----------
-- 1  | Maria    | cancelled | 2025-11-03
-- 2  | Tom      | shipped   | 2025-12-19
-- 3  | Priya    | cancelled | 2026-02-11
-- 4  | Maria    | shipped   | 2026-03-02

CREATE TEMPORARY TABLE orders_copy AS
SELECT * FROM orders;

DELETE FROM orders_copy
WHERE status = 'cancelled' AND order_date < '2026-01-01';

SELECT * FROM orders_copy;

-- Output:
-- id | customer | status    | order_date
-- ---+----------+-----------+-----------
-- 2  | Tom      | shipped   | 2025-12-19
-- 3  | Priya    | cancelled | 2026-02-11
-- 4  | Maria    | shipped   | 2026-03-02

-- The real table still has every row
SELECT COUNT(*) AS order_count FROM orders;
-- Output: 4