Skip to content
Tutorials Center
My learning

Topics

Lesson 67 of 125 ยท Designing Databases

Temporary Tables, CREATE TABLE AS and SELECT INTO

Copy tables and results with CREATE TABLE AS and SELECT INTO, build temporary tables, and compare SQLite, PostgreSQL, MySQL, SQL Server and Oracle.

Sooner or later you need a table you don't want to design by hand. Maybe you want a snapshot of today's prices before a risky update, a smaller table to test a report against, or a scratch area that holds the output of a heavy query so that the next five queries can reuse it. SQL has three tools for that, and they are easy to mix up:

  • CREATE TABLE ... AS SELECT (CTAS) creates a new permanent table from a query result.
  • SELECT ... INTO does the same job in SQL Server and PostgreSQL, with different grammar.
  • Temporary tables are ordinary tables that only your session can see and that vanish on their own.

This lesson shows each one, what gets copied and (more importantly) what doesn't, and how the spelling changes across databases. Examples run in SQLite on the shop sample database; the other dialects appear as read-only blocks.

CREATE TABLE AS: a table from a query

The syntax is CREATE TABLE new_name AS followed by any SELECT. The database runs the query, builds columns matching the result, and fills the table in one step:

SQL A snapshot with CREATE TABLE AS ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

The snapshot is a plain table that holds a copy of the rows at one moment. Later changes to products don't reach it, and it doesn't reach back. That makes CTAS the standard tool for backups before a migration, archive tables and test fixtures.

What CTAS does not copy

The new table gets column names and (roughly) types from the query. It usually does not get anything else. Look at what the snapshot lost:

SQL The constraints disappear ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

The snapshot has no PRIMARY KEY, no NOT NULL, no CHECK, no foreign keys, no indexes and no default values, so the same row can be inserted twice without complaint. After CTAS you normally add what you need:

SQL
CREATE UNIQUE INDEX idx_snapshot_id ON price_snapshot (id);

Databases differ in the details (MySQL, for example, keeps column types and nullability), but none of them copies keys or indexes. Never assume the copy is constraint-complete.

Structure only, no rows

To copy just the shape, make the query return nothing. A condition that is never true does it, and it works in every database:

SQL An empty copy ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

The archive has the same columns but zero rows. Several databases offer a more faithful clone of the structure: CREATE TABLE copy (LIKE original INCLUDING ALL) in PostgreSQL, CREATE TABLE copy LIKE original in MySQL. Both carry indexes and defaults. The MySQL copying lesson goes through that case.

People sometimes name the three copy styles. A simple clone is CTAS (CREATE TABLE ... AS SELECT): data and column types, but no keys or indexes. A shallow clone copies only the structure (LIKE, or the WHERE 1 = 0 trick) and leaves the new table empty. A deep clone copies structure and rows with their constraints and indexes: create the table with LIKE ... INCLUDING ALL (PostgreSQL) or LIKE (MySQL), then INSERT INTO copy SELECT * FROM original. The drawback of the simple clone is exactly what you saw above: it quietly drops the rules that keep data valid.

PostgreSQL also accepts WITH NO DATA in place of the 1 = 0 trick: CREATE TABLE t AS SELECT ... WITH NO DATA;.

SELECT INTO

SQL Server and PostgreSQL support another way to make a table from a query, placing INTO between the select list and FROM:

SQL
-- SQL Server and PostgreSQL (read-only here)
SELECT id, name, price
INTO price_snapshot
FROM products
WHERE price >= 50;

The result is the same as CTAS. SQL Server has no CREATE TABLE AS at all, so SELECT ... INTO is its only one-step copy; adding INTO #price_snapshot makes it a temporary table. In PostgreSQL both forms exist, but the documentation recommends CREATE TABLE AS, because SELECT INTO means something else inside PL/pgSQL functions (assigning to variables). MySQL and SQLite have no SELECT ... INTO table. (MySQL uses SELECT ... INTO @variable or INTO OUTFILE, which are different features.)

To add rows to an existing table you don't use either one. That is INSERT ... SELECT.

Temporary tables

A temporary table looks and behaves like any other table, but:

  • Only your session sees it. Another connection can have its own temporary table with the same name.
  • It disappears automatically, normally when your session ends (and optionally earlier, at commit).
  • It doesn't pollute the schema. Nobody has to clean up, and nothing needs to be backed up.

That makes it the right home for intermediate results in long scripts: build a filtered set once, index it if useful, and join against it several times.

SQL A temporary working table ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

In SQLite, CREATE TEMP TABLE (or TEMPORARY) puts the table in a separate temp schema. Its listing is sqlite_temp_master, and the table disappears when the connection closes. Because this runner builds a fresh database for each run, it is gone when the run ends.

How each database spells it

Database Create syntax Lifetime
SQLite CREATE TEMP TABLE t (...) Until the connection closes
PostgreSQL CREATE TEMP TABLE t (...) ON COMMIT DROP (or PRESERVE ROWS, DELETE ROWS) Session, or transaction with ON COMMIT
MySQL CREATE TEMPORARY TABLE t (...) Until the connection closes
SQL Server CREATE TABLE #t (...) (session-local) or ##t (global) #t: session or procedure; ##t: until the last user disconnects
Oracle CREATE GLOBAL TEMPORARY TABLE t (...) ON COMMIT DELETE ROWS The definition is permanent; each session sees only its own rows. Oracle 18c adds private temporary tables

Oracle is the odd one out: a global temporary table is a normal schema object whose data is private per session or transaction. The other databases create a genuinely per-session table.

Pitfalls

  • They can hide a permanent table. In MySQL and SQLite a temporary table with the same name as a permanent one wins inside your session. Use a prefix like tmp_ to avoid confusion.
  • Connection pools break assumptions. If your application reuses connections, a temporary table can outlive a request and clash with the next one. Drop temp tables explicitly (DROP TABLE IF EXISTS).
  • They cost real I/O. Temp tables are written to disk (or cache) and can bloat tempdb in SQL Server. Don't copy millions of rows when a join would do.
  • Inside stored procedures they are scoped to the procedure in SQL Server and survive until the connection ends in MySQL, so test lifetime rules on your own engine.
  • Statistics matter. A big temp table with no index and no statistics can drive bad query plans. Create an index after loading it.

Local vs global temporary tables and table variables

"Temporary" hides two separate questions: who can see the table (scope) and when it disappears (lifetime). Databases answer them differently, and SQL Server and Oracle add variations worth knowing.

SQL Server: #local, ##global and table variables

SQL Server decides scope by the name:

Kind Syntax Visible to Dropped when
Local temp table CREATE TABLE #orders_tmp (...) Your session (and procedures it calls) The session ends, or the procedure that created it returns
Global temp table CREATE TABLE ##orders_tmp (...) Every session The creating session ends and no other session is still using it
Table variable DECLARE @orders_tmp TABLE (...) The current batch, function or procedure only The batch ends
SQL
-- SQL Server
CREATE TABLE #recent (order_id INT PRIMARY KEY, total DECIMAL(10,2));
INSERT INTO #recent
SELECT id, total FROM orders WHERE ordered_at >= DATEADD(DAY, -7, GETDATE());

DECLARE @top3 TABLE (order_id INT, total DECIMAL(10,2));
INSERT INTO @top3
SELECT TOP (3) order_id, total FROM #recent ORDER BY total DESC;

SELECT * FROM @top3;
DROP TABLE #recent;

Global temp tables are rarely the right choice. Any session can read, change or drop them, two scripts that use the same name collide, and the table can vanish in the middle of a long job when its creator disconnects. Use them only for deliberate sharing, such as one session handing a work list to another during a migration. A permanent "staging" table is usually cleaner.

Table variables versus #temp tables. Both live in tempdb, despite the myth that table variables stay in memory. The practical differences:

  • A table variable is scoped to its batch and ignores transaction rollback: rows inserted into it survive a ROLLBACK, which can be handy for logging errors.
  • A #temp table has statistics and can have additional indexes created after the fact, so the optimizer plans well for large row counts. Table variables historically were estimated as having one row; newer versions (deferred compilation, SQL Server 2019+) improve that, but #temp is still the safer choice above a few hundred rows.
  • Table variables can be passed to procedures only as table-valued parameters.

A good rule: table variable for a handful of rows inside one procedure, #temp table for anything big or reused by several statements.

PostgreSQL: ON COMMIT

PostgreSQL has one kind of temporary table, private to the session, but lets you pick its lifetime inside transactions:

SQL
-- PostgreSQL
CREATE TEMP TABLE batch_ids (id int) ON COMMIT DROP;      -- gone at COMMIT
CREATE TEMP TABLE scratch   (id int) ON COMMIT DELETE ROWS; -- table stays, rows go at COMMIT
CREATE TEMP TABLE keepers   (id int) ON COMMIT PRESERVE ROWS; -- the default

ON COMMIT DROP is ideal for a table that exists only to serve one transaction. Because the table is dropped automatically, a pooled connection cannot carry it over into the next request. There is no global temporary table, but an unlogged table (CREATE UNLOGGED TABLE) is a shared, faster-than-normal table that is emptied after a crash.

Oracle: global temporary tables

In Oracle, "global" means something else entirely. The table definition is permanent and visible to everyone, but the rows are private to each session or transaction:

SQL
-- Oracle
CREATE GLOBAL TEMPORARY TABLE cart_scratch (
  product_id NUMBER,
  qty        NUMBER
) ON COMMIT DELETE ROWS;    -- or ON COMMIT PRESERVE ROWS: rows last until the session ends

Two sessions can both insert into cart_scratch and will never see each other's rows. You create the table once, as part of your schema, and application code just uses it. Oracle 18c added private temporary tables (CREATE PRIVATE TEMPORARY TABLE ora$ptt_name ...) whose definition is also session-local, and which behave like the temp tables of the other databases.

Comparison

SQLite PostgreSQL MySQL SQL Server Oracle
Session-private table TEMP TEMP TEMPORARY #name private temp table (18c+)
Visible to all sessions no no (use UNLOGGED) no ##name table definition only
End-of-transaction option no ON COMMIT ... no no ON COMMIT ...
Table variable no no no DECLARE @t TABLE no (use collections)

The SQLite sandbox on this page cannot show cross-session visibility, but it can show the "separate catalog" idea: a TEMP table never appears in the permanent catalog.

SQL A temp table is private and separate

Press Run (or Ctrl + Enter) to see the result here.

Temporary table, CTE or view?

You have three ways to name an intermediate result. Pick by how long it must live and how often it is reused:

Tool Stores data? Lives Best for
CTE No, re-evaluated (usually) One statement Readability, one-time use
View No Permanently, as a definition Shared, always-fresh logic
Temp table Yes Session Reuse across many statements, indexing, big reused results
CTAS table Yes Permanently Snapshots, archives, reporting tables

Start with a CTE. Reach for a temp table when the same expensive result feeds several queries, or when you need an index on the intermediate data.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. Which of these does CREATE TABLE copy AS SELECT * FROM original usually NOT copy?

    CTAS copies column names, types and data. Keys, indexes, foreign keys, checks and defaults are generally not carried over.
  2. 2. How can you copy only a table's columns, with no rows, using CTAS in any database?

    A condition that is never true returns zero rows, but the result still defines the columns, so the new table is empty.
  3. 3. Which database creates a temporary table with a name that starts with #?

    SQL Server uses #name for session-local and ##name for global temporary tables.
  4. 4. What is different about Oracle's global temporary tables?

    In Oracle the table is a permanent schema object; only its data is session-private and cleared at commit or disconnect.

Summary

  • CREATE TABLE ... AS SELECT builds a permanent table from a query result; SELECT ... INTO is the SQL Server and PostgreSQL spelling.
  • A CTAS copy has the data and column types but no keys, indexes, constraints or defaults; add what you need afterwards.
  • WHERE 1 = 0 (or WITH NO DATA, LIKE) copies structure without rows.
  • Temporary tables are private to a session and disappear automatically, but each database creates them differently.
  • SQL Server has #local (session), ##global (all sessions) and @table variable (one batch); PostgreSQL has ON COMMIT DROP | DELETE ROWS; Oracle global temporary tables have a permanent definition with private rows.
  • Prefer CTEs for single-statement logic, views for shared logic, and temp tables for reused or indexed intermediate results.

Next: Keys, the constraint that CTAS forgets to copy.