SQL Temporary Tables Explained: Create, Use, Index and Drop Them
A SQL temporary table holds interim results for your session and disappears when you are done. Learn the syntax in SQL Server, PostgreSQL, MySQL, Oracle and SQLite, how session scope works, when to choose a temp table over a CTE, and a name-shadowing trap, with tested queries.
- A SQL temporary table is a real table that only your session can see, and the database drops it automatically when the session ends.
- Unlike a CTE, it can be reused across many statements, updated, and indexed.
- Syntax differs:
#namein SQL Server,CREATE TEMP TABLEin PostgreSQL and SQLite,CREATE TEMPORARY TABLEin MySQL, and global or private temporary tables in Oracle. - A temp table with the same name as a real table hides the real one. In our test,
SELECT count(*) FROM ordersdropped from 5 to 0.
Long reports and data fixes often have stages: filter the raw data, calculate totals, join the result to three other tables, then update something. Squeezing all of that into one giant query makes it hard to read and harder to debug. A SQL temporary table lets you save each stage as a real table, inspect it, index it and reuse it, without leaving clutter behind.
We tested every example on SQLite 3.50.4 through Python’s sqlite3 module, including the multi-connection scope tests. Syntax for other databases is in a table further down.
What is a temporary table?
A temporary table behaves like a normal table: it has columns and types, you can insert, update, delete and join it, and in most databases you can index it. The differences are:
- Visibility. Only the session (connection) that created it can see it.
- Lifetime. It is dropped automatically when that session ends, or earlier if you drop it.
- Storage. It lives in a separate temporary area, such as
tempdbin SQL Server or thetempschema in SQLite.
Why use a temporary table?
- Break a complex query into readable steps.
- Compute an expensive result once and reuse it in several statements.
- Stage imported data for validation before inserting it into real tables.
- Hold a list of IDs to process in a batch job.
- Debug: look at intermediate results without changing the final query.
Creating a temporary table
Our sample data has five orders:
CREATE TABLE orders(id INTEGER PRIMARY KEY, customer TEXT, amount INT, status TEXT);
INSERT INTO orders VALUES (1,'Asha',1200,'paid'), (2,'Ravi',300,'paid'),
(3,'Asha',800,'refunded'), (4,'Meera',2500,'paid'), (5,'Ravi',900,'paid');
The quickest way to create a temp table is from a query:
CREATE TEMP TABLE big_spenders AS
SELECT customer, SUM(amount) AS spent
FROM orders
WHERE status = 'paid'
GROUP BY customer
HAVING SUM(amount) >= 1000;
SELECT * FROM big_spenders ORDER BY spent DESC;
| customer | spent |
|---|---|
| Meera | 2500 |
| Asha | 1200 |
| Ravi | 1200 |
Asha’s refunded order was excluded, and Ravi qualified because the two paid orders add up to 1,200. You can also create an empty temp table with explicit columns using CREATE TEMP TABLE name (col type, ...) and fill it with INSERT ... SELECT. Our GROUP BY and HAVING guide explains the aggregation part.
Using, updating and indexing it
CREATE INDEX temp.ix_big ON big_spenders(customer);
UPDATE big_spenders SET spent = spent * 1.1 WHERE customer = 'Meera';
SELECT o.id, o.customer, b.spent
FROM orders o JOIN big_spenders b USING (customer)
ORDER BY o.id;
| id | customer | spent |
|---|---|---|
| 1 | Asha | 1200 |
| 2 | Ravi | 1200 |
| 3 | Asha | 1200 |
| 4 | Meera | 2750.0 |
| 5 | Ravi | 1200 |
We indexed the temp table, updated one row and joined it back to the main table, three things a CTE cannot do. The index matters when a temp table holds thousands of rows and is joined repeatedly. See our SQL indexes guide for how to choose columns.
Session scope: who can see it?
We tested visibility with two connections to the same database file:
a = sqlite3.connect("shop.db")
a.execute("CREATE TEMP TABLE scratch(x)")
a.execute("INSERT INTO scratch VALUES (1)")
b = sqlite3.connect("shop.db")
b.execute("SELECT * FROM scratch")
other connection: OperationalError no such table: scratch
same connection: (1,)
after reconnect: OperationalError no such table: scratch
The second connection could not see the table at all, and after the first connection closed and reopened, the table was gone. This isolation is what makes temp tables safe: two users running the same report each get their own copy, with no name clashes. It also means connection pools can surprise you. If your application reuses pooled connections, a temp table may outlive one request and still be there for the next. Drop temp tables explicitly in pooled environments.
Syntax by database
| Database | Create | Notes |
|---|---|---|
| SQL Server | CREATE TABLE #big (...) or SELECT ... INTO #big |
# is local to the session; ## is global and visible to all sessions. Stored in tempdb. |
| PostgreSQL | CREATE TEMP TABLE big AS ... |
ON COMMIT DROP or ON COMMIT DELETE ROWS tie it to a transaction. |
| MySQL | CREATE TEMPORARY TABLE big ... |
Drop with DROP TEMPORARY TABLE to avoid touching a real table. |
| Oracle | CREATE GLOBAL TEMPORARY TABLE ... |
The definition is permanent; the rows are private per session. 18c added private temporary tables. |
| SQLite | CREATE TEMP TABLE big ... |
Lives in the temp schema; listed in sqlite_temp_master. |
One SQL Server detail: a temp table created inside a stored procedure is dropped when that procedure finishes, not when the session ends.
The name-shadowing trap
What happens if a temp table has the same name as a real table? We created a temporary orders while the real orders held five rows:
CREATE TEMP TABLE orders(id INT, note TEXT);
SELECT count(*) FROM orders; -- 0
SELECT count(*) FROM main.orders; -- 5
DROP TABLE temp.orders;
SELECT count(*) FROM orders; -- 5
The temporary table silently hid the real one. Every unqualified query in that session now read the empty temp table. Imagine running a cleanup DELETE FROM orders WHERE ... while unsure which table you are hitting. SQL Server avoids this with the # prefix, but PostgreSQL, MySQL and SQLite all let a temporary table shadow a permanent one of the same name. Use a clear prefix such as tmp_ for every temp table.
Tip: in scripts, qualify names when it matters: temp.orders or main.orders in SQLite, pg_temp.orders or public.orders in PostgreSQL.
Temp table vs CTE
A common table expression is a named subquery that exists only for one statement. We ran the same “big spenders” logic as a CTE, then tried to reuse the name in the next statement:
WITH paid AS (
SELECT customer, SUM(amount) AS spent FROM orders
WHERE status = 'paid' GROUP BY customer
)
SELECT * FROM paid WHERE spent >= 1000 ORDER BY spent DESC;
-- Meera 2500, Asha 1200, Ravi 1200
SELECT * FROM paid;
-- OperationalError: no such table: paid
| CTE | Temporary table | |
|---|---|---|
| Lifetime | One statement | Whole session |
| Reusable across statements | No | Yes |
| Indexes | No | Yes |
| Can be updated | No | Yes |
| Cleanup needed | None | Automatic at session end, or DROP |
| Best for | Readable single queries, recursion | Multi-step scripts, large reused results |
Start with a CTE. Switch to a temp table when you need the result in more than one statement, when the result is large and joined repeatedly, or when the optimiser handles the CTE badly. Checking the plan with EXPLAIN, as in our query optimisation guide, tells you which is faster for your data.
Other alternatives
- Table variables in SQL Server (
DECLARE @t TABLE (...)) suit small row counts but have limited statistics. - Views are permanent saved queries, shared by everyone. Our SQL views guide compares them.
- Derived tables (subqueries in
FROM) are like an inline CTE. - Real staging tables make sense when several sessions or jobs must share the intermediate data.
Temp tables in testing
Temporary tables are handy in database tests. A test can create its own fixture data in temp tables, run the query under test against them, and leave nothing behind for the next test, because the session ends when the connection closes. They are also useful for comparing results: load expected rows into one temp table and actual rows into another, then use EXCEPT in both directions to list any differences. Our SQL questions for testers cover more database checks.
Cleaning up
DROP TABLE IF EXISTS big_spenders;
Automatic cleanup at session end is a safety net, not a plan. Long-lived connections, connection pools and SQL Server tempdb pressure all argue for dropping temp tables as soon as you are done. IF EXISTS makes the drop safe to run twice.
Best practices
- Prefix names, such as
tmp_big_spenders, to avoid shadowing real tables. - Create only the columns and rows you need.
- Index temp tables that are large and joined repeatedly.
- Drop them explicitly, especially with pooled connections.
- Prefer a CTE for single-statement logic, and a temp table for multi-step work.
- Do not use global temp tables to share data between users; use a real table with proper permissions.
Final thoughts
A SQL temporary table is a simple, safe scratchpad for multi-step SQL. It gives you a real table that only your session sees, that you can index and update, and that cleans itself up. Learn the syntax for your database, reach for a CTE first and a temp table when you need reuse, prefix the names to avoid shadowing real tables, and drop them when you are done.
FAQ
What is a temporary table in SQL?
It is a table visible only to the session that created it. The database drops it automatically when the session ends, which makes it ideal for intermediate results.
When is a temporary table deleted?
When the session or connection that created it ends, or earlier if you drop it. In SQL Server a temp table created in a stored procedure is dropped when the procedure finishes.
What is the difference between a temp table and a CTE?
A CTE lasts for one statement and cannot be indexed. A temp table lasts for the session, can be reused in many statements, updated and indexed.
Can other users see my temporary table?
No. Local temporary tables are private to your session. SQL Server global temp tables with two hash signs are the exception and are visible to all sessions.
Can I create an index on a temporary table?
Yes. Most databases, including SQL Server, PostgreSQL, MySQL and SQLite, allow indexes on temporary tables, which helps when they hold many rows.
Keep learning
Need help with testing, WordPress or SEO?
We help teams ship better software and grow their sites. Tell us what you are working on and we will suggest a clear next step.