1. Home
  2. Blog
  3. Databases & SQL

Databases & SQL

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.

9 min read
SQL temporary tables: create, use and drop

Key takeaways

  • 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: #name in SQL Server, CREATE TEMP TABLE in PostgreSQL and SQLite, CREATE TEMPORARY TABLE in 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 orders dropped 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 tempdb in SQL Server or the temp schema 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

  1. Prefix names, such as tmp_big_spenders, to avoid shadowing real tables.
  2. Create only the columns and rows you need.
  3. Index temp tables that are large and joined repeatedly.
  4. Drop them explicitly, especially with pooled connections.
  5. Prefer a CTE for single-statement logic, and a temp table for multi-step work.
  6. 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

Work with Stack Quora

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.

Keep reading

More guides on testing, AI & modern development

Enjoyed this article? Pick a topic and jump straight into our newest tutorials.

Artificial Intelligence

Jaccard Similarity: Formula, Python, MinHash

Jaccard similarity measures overlap between two sets as intersection over union. Work the formula, compute it in Python for words…

Read article →
Software Development

Unix Timestamp Explained: Seconds vs ms

A Unix timestamp counts seconds since 1 January 1970 UTC. Learn seconds vs milliseconds, converting in Python, JavaScript and SQL,…

Read article →
DevOps

Git Hooks: Pre-commit and Commit-msg Examples

Git hooks run scripts at key moments such as commit and push. See tested pre-commit, commit-msg and prepare-commit-msg hooks, sharing…

Read article →
Test Automation

API Testing in Python: Pytest + Requests Suite

Build an API testing suite in Python with pytest and requests: fixtures, status codes, headers, JSON schema validation, CRUD flows,…

Read article →
Software Testing

Test Design Techniques: 9 Methods + Examples

Test design techniques help you write fewer tests that catch more bugs. Learn 9 black-box, white-box and experience-based methods with…

Read article →
Databases & SQL

SQL Temporary Table: Create, Use and Drop It

SQL temporary tables store interim results for one session. Learn syntax for SQL Server, PostgreSQL, MySQL, Oracle and SQLite, session…

Read article →
Databases & SQL

SQL CROSS JOIN: Every Combination + Examples

SQL CROSS JOIN returns every combination of rows from two tables. See the row math, product variants, calendar grids that…

Read article →
Software Development

JavaScript Date: Format, Parse, Timezone Bugs

JavaScript Date explained with tested Node.js code: zero-based months, the date-only UTC parsing trap, toISOString, getTimezoneOffset, Intl formatting, date math…

Read article →
Software Development

Python Requests: GET, POST, Timeouts, Retries

Python requests explained with code tested against a local API: GET with params, POST json vs data, headers and auth,…

Read article →
Software Development

Python Deepcopy vs Copy: Fix Nested Lists

Python deepcopy vs shallow copy explained with tested code: aliasing, nested lists, the [[0]*3]*3 trap, memo and cycles, custom __deepcopy__,…

Read article →
Artificial Intelligence

TF-IDF Explained: Formula and Worked Example

TF-IDF scores a word by how often it appears in a document and how rare it is across all documents….

Read article →
Software Development

Base64 Encoding Explained, With Python Code

Base64 encoding turns bytes into 64 safe text characters. See the bit-level steps, padding, URL-safe base64, the 33% size cost,…

Read article →
DevOps

Git Tag: Create, Push and Delete Release Tags

Git tag explained with tested commands: annotated vs lightweight tags, listing and sorting, pushing with follow-tags, deleting local and remote…

Read article →
Test Automation

Pytest Coverage: pytest-cov and Branch Gaps

Measure pytest coverage with pytest-cov: term-missing reports, branch coverage, HTML reports, pyproject settings and a fail-under gate for CI, with…

Read article →
Software Testing

Error Guessing in Testing: Checklist + Demo

Error guessing uses tester experience to predict where code breaks. Get a ready checklist of risky inputs, a step by…

Read article →
Databases & SQL

SQL LAG and LEAD: Compare Rows, 5 Examples

SQL LAG and LEAD read values from the previous or next row without a self join. Five tested examples: change,…

Read article →
Databases & SQL

SQL STRING_AGG: Combine Rows Into One String

SQL STRING_AGG joins values from many rows into one string per group. See syntax for PostgreSQL, SQL Server, MySQL GROUP_CONCAT,…

Read article →
Software Development

JavaScript Classes: Private Fields and Extends

JavaScript classes explained with tested Node.js code: constructors, methods, #private fields, getters, static members, extends and super, plus the errors…

Read article →
Artificial Intelligence

BM25 Explained: How Keyword Search Ranks Results, With Python Code and Hybrid Search

BM25 explained with a pure Python implementation we ran: the formula, IDF, term saturation, the k1 and b parameters, why…

Read article →
DevOps

Git Bisect Explained: Find the Commit That Broke Your Code, Manually or Automatically

Git bisect explained with a tested repository: find the one bad commit among 41 in 5 test runs, automate it…

Read article →
Test Automation

pytest Markers Explained: Custom Marks, skip, skipif, xfail and -m Filters

pytest markers explained with a tested suite: register custom marks, run only smoke tests with -m, combine filters with and,…

Read article →
Software Testing

Positive vs Negative Testing: Differences, 24 Real Test Cases and a Checklist

Positive vs negative testing explained with a real signup form: 6 positive and 18 negative test cases run in pytest,…

Read article →
Databases & SQL

SQL ORDER BY Multiple Columns: ASC, DESC, NULLS LAST, Case and Custom Order

SQL ORDER BY with multiple columns explained with tested queries: mixed ASC and DESC, tie-breakers, NULLS FIRST and LAST, case-insensitive…

Read article →
Databases & SQL

SQL Running Total: SUM OVER, Per-Group Totals, Moving Sums and the RANGE Trap

How to calculate a SQL running total with SUM() OVER: per-group totals, daily cumulative sums, moving sums, percent of total,…

Read article →
Artificial Intelligence

Cosine Similarity Explained: Formula, Worked Example and Python Code

Cosine similarity explained simply: the formula, a worked example by hand, pure Python and NumPy code, cosine vs Euclidean distance…

Read article →
DevOps

Git Squash Commits: 4 Easy Ways, Tested Step by Step (rebase -i, reset, merge)

Git squash commits explained with 4 tested methods: interactive rebase, reset –soft, merge –squash and fixup with autosquash. Before-and-after logs,…

Read article →
Test Automation

pytest parametrize Explained: 7 Patterns with Tested Examples

pytest parametrize explained with 7 tested patterns: basic cases, readable ids, stacked decorators, pytest.param with xfail and skip, indirect fixtures…

Read article →
Software Testing

Retesting vs Regression Testing: 8 Key Differences with a Real Bug Fix Example

Retesting vs regression testing explained with a real bug fix run in pytest: retesting confirmed the fix, the regression run…

Read article →
Artificial Intelligence

RAG Chunking Strategies Explained: Fixed, Overlap, Sentence, Recursive and Headings, Measured

RAG chunking strategies compared on one document with tiktoken 0.14: fixed-size, overlap, sentence packing, recursive splitting and heading-based chunks, measuring…

Read article →
DevOps

Git Stash Explained: Save, Apply, Pop and Untracked Files, Tested

Git stash explained with commands we ran on Git 2.56: push with a message, untracked files, list and show, apply…

Read article →
Software Testing

Pairwise Testing Explained: How All-Pairs Cuts 216 Tests to 13, Tested

Pairwise testing explained with a generator we ran in Python 3.13: a checkout with 216 combinations covered by 13 tests,…

Read article →
Test Automation

Test Doubles Explained: Dummy, Stub, Spy, Mock and Fake, with Tested Python Code

Test doubles explained with one Python example run in pytest 9.2: dummy, stub, spy, mock and fake, when to use…

Read article →
Artificial Intelligence

LLM Context Window Explained: Tokens, Limits and How to Fit More, Measured

The LLM context window explained with token counts we measured using tiktoken: words vs tokens for prose, code and Hindi,…

Read article →
DevOps

Git Cherry-Pick Explained: Copy the Commit You Need, Safely, Tested

Git cherry-pick explained with commands we ran in Git 2.56: copying a hotfix to a release branch, new hashes and…

Read article →
Test Automation

Snapshot Testing Explained: How It Works, When It Helps and When It Hurts

Snapshot testing explained with a pytest and syrupy demo we ran: creating snapshots, reading the diff after a code change,…

Read article →
Software Testing

Mutation Testing Explained: Why 100% Coverage Still Missed Our Bug

Mutation testing explained with a small mutation tool we built and ran: 14 mutants, a test suite with 100% line…

Read article →