---
title: "Temporary table"
description: "A temporary table is a table scoped to a single session (or transaction) that is automatically dropped when the session ends, used for intermediate results that don't need to persist."
canonical: "https://motherduck.com/glossary/temporary-table/"
related:
  - title: "TEMPORARY TABLES | MotherDuck Docs"
    url: "https://motherduck.com/docs/sql-reference/motherduck-sql-reference/temporary-tables/"
  - title: "DuckLake Architecture Deep Dive"
    url: "https://motherduck.com/blog/ducklake-architecture-deep-dive/"
  - title: "DuckDB statements | MotherDuck Docs"
    url: "https://motherduck.com/docs/sql-reference/duckdb-sql-reference/duckdb-statements/"
---

# Temporary table

> A temporary table is a table scoped to a single session (or transaction) that is automatically dropped when the session ends, used for intermediate results that don't need to persist.

## Overview
Temporary tables behave like regular tables — you can `INSERT`, `UPDATE`, `SELECT`, and index them — but they live only for the duration of the session (or, in some engines, the transaction) that created them, and are invisible to other sessions. They're useful for staging intermediate results in a multi-step ETL script, breaking up a complex query into named pieces, or avoiding the need to clean up scratch tables manually.

```sql
CREATE TEMPORARY TABLE staging_orders AS
SELECT * FROM read_csv('incoming_orders.csv');

UPDATE staging_orders SET status = 'pending' WHERE status IS NULL;

INSERT INTO orders SELECT * FROM staging_orders WHERE status = 'pending';
-- staging_orders is dropped automatically when the session ends
```

<glossary-callout guide="duckdb-cheatsheet-full" />

## Temporary table vs CTE vs view
A CTE (`WITH ... AS (...)`) exists only for the duration of a single query and can't be indexed or reused across statements. A `VIEW` persists indefinitely and re-runs its query on every read. A temporary table sits between the two: it persists across multiple statements within a session (letting you build on intermediate results step by step) but disappears automatically when the session closes, requiring no manual cleanup.

## DuckDB specifics
DuckDB supports both `CREATE TEMP TABLE` and `CREATE TEMPORARY TABLE` (equivalent keywords), and they work whether you're connected to an in-memory database or a persistent DuckDB file — temporary tables and their data are always held in memory regardless of the main database's storage, and are dropped when the connection closes. This makes them a lightweight scratch space for multi-step scripts (e.g., DuckDB running as an ELT engine) without writing intermediate results to disk.