How to Create and Use Temporary Tables in SQLite

SQLite temporary tables let you hold intermediate data while working with a database. Create one with TEMP or TEMPORARY, query and change its rows with ordinary SQL, then remove it when finished. A temporary table belongs to the database connection that created it, so its lifetime is tied to that connection.

Create a temporary table in SQLite

Use CREATE TEMP TABLE (or the equivalent CREATE TEMPORARY TABLE) to define a temporary table. The following example uses Python’s built-in sqlite3 module to run a complete in-memory SQLite workflow. The source table is needed only when the SELECT in a CREATE TABLE AS SELECT statement reads from one; a temporary table can also be defined directly with columns and values.

Run this example:

import sqlite3

connection = sqlite3.connect(":memory:")
connection.execute("CREATE TEMP TABLE users (user_id INTEGER, name TEXT, age INTEGER)")
connection.executemany(
    "INSERT INTO users (user_id, name, age) VALUES (?, ?, ?)",
    [(1, "Ari", 30), (2, "Bo", 25)],
)

connection.execute(
    "CREATE TEMP TABLE recent_users AS "
    "SELECT user_id, name FROM users WHERE age >= 18"
)
rows = connection.execute("SELECT user_id, name, age FROM users ORDER BY user_id").fetchall()
print(rows)

connection.execute("UPDATE users SET age = 31 WHERE user_id = 1")
connection.execute("DELETE FROM users WHERE user_id = 2")
connection.execute("DROP TABLE IF EXISTS temp.users")
connection.close()

The Python example builds recent_users with CREATE TEMP TABLE ... AS SELECT while users exists on the same connection. SQLite derives the new table’s columns from the query result. If a query reads from a source table, that source must already exist when the statement runs; otherwise, no pre-existing table is required. TEMP and TEMPORARY are accepted alternatives, so choose either spelling consistently.

Understand temporary-table scope and lifetime

SQLite stores temporary tables in the connection’s temporary database. Other connections do not share that connection’s temporary table, even if they use the same database file or the same table name. A temporary table disappears when its owning connection closes. This is a connection lifetime, not an application session timeout, so code that reuses a connection can continue to use the table until it closes that connection or drops the table explicitly.

This scope makes a temporary table useful for intermediate results that should not be kept as persistent application data. It also means that closing a connection discards any temporary rows you still need. Copy required results into a persistent table or retrieve them before closing the connection.

Query a temporary table

Query a temporary table with ordinary SELECT syntax, for example SELECT user_id, name FROM users WHERE age >= 18;. The query addresses the temporary table when its name does not conflict with a persistent table; to identify it explicitly, qualify it as temp.users. The data remains selectable only through the connection that owns it.

A common table expression can also feed a query that reads temporary data. WITH introduces a named query result for the statement, and it is not restricted to statements containing only one SELECT. For example, the inline query WITH adults AS (SELECT user_id, name FROM temp.users WHERE age >= 18) SELECT name FROM adults; reads from the temporary table and returns the selected names. Use a direct SELECT when no intermediate query name makes the statement clearer.

Insert, update, and delete rows in a temporary table

Use INSERT, UPDATE, and DELETE as you would for other SQLite tables, while making sure each statement targets the temporary table. For example, INSERT INTO temp.users (user_id, name, age) VALUES (3, 'Cy', 28); adds a row. To change a selected row, use UPDATE temp.users SET age = 29 WHERE user_id = 3;. The WHERE condition limits the change to matching rows; without it, an update affects every row in the target table.

To remove selected rows, use DELETE FROM temp.users WHERE user_id = 3;. SQLite’s syntax does not put an asterisk after DELETE. Omitting WHERE deletes all rows from that temporary table, while leaving the table itself in place. These operations change the temporary data only; they do not change a persistent table with the same name. Keep the target name and schema clear when a database also contains a persistent users table.

Drop a temporary table safely

Remove a temporary table explicitly with DROP TABLE temp.users;. The temp. qualifier identifies the temporary schema, which can help avoid dropping a persistent table that has the same name. If the table might already have been removed, use DROP TABLE IF EXISTS temp.users;. The IF EXISTS clause makes the statement safe to run when that temporary table is absent.

Before dropping the table or closing its connection, copy or retrieve any rows that must be kept. Dropping removes the temporary table and its rows; closing the owning connection also ends the table’s lifetime. Use a persistent table for data that must remain available after that connection closes.