The jOOQ User Manual SQL building SQL Statements (DDL) The CREATE statement CREATE TABLE Global temporary tables

previous : next

Global temporary tables

Applies to ✅ Open Source Edition   ✅ Express Edition   ✅ Professional Edition   ✅ Enterprise Edition

A GLOBAL TEMPORARY table is a table whose meta data is shared among all sessions or transaction, but where each session or transaction contains its own data. It acts like a table with a session or transaction based policy enabled.

// Create a new temporary table
create.createGlobalTemporaryTable("book_archive")
      .column("column1", INTEGER)
      .execute();

Historically, jOOQ also supported the CREATE TEMPORARY TABLE syntax via DSLContext.createTemporaryTable(Table) (without explicit GLOBAL or LOCAL keyword):

// Create a new temporary table
create.createTemporaryTable("book_archive")
      .column("column1", INTEGER)
      .execute();

• GLOBAL TEMPORARY if it's supported by a dialect
• LOCAL TEMPORARY otherwise

Dialect support

This example using jOOQ:

createGlobalTemporaryTable("book_archive")
      .column("column1", INTEGER)

Translates to the following dialect specific expressions:

Aurora Postgres, H2, Vertica

CREATE GLOBAL TEMPORARY TABLE book_archive (
  column1 int
)

DB2, Firebird, Hana, Teradata

CREATE GLOBAL TEMPORARY TABLE book_archive (
  column1 integer
)

MemSQL

CREATE ROWSTORE GLOBAL TEMPORARY TABLE book_archive (
  column1 int
)

Oracle

CREATE GLOBAL TEMPORARY TABLE book_archive (
  column1 number(10)
)

SQLServer

CREATE TABLE ##book_archive (
  column1 int
)

ASE, Access, Aurora MySQL, BigQuery, ClickHouse, CockroachDB, Databricks, Derby, DuckDB, Exasol, HSQLDB, Informix, MariaDB, MySQL, Postgres, Redshift, SQLDataWarehouse, SQLite, Snowflake, Sybase, Trino, YugabyteDB

/* UNSUPPORTED */

The jOOQ User Manual SQL building SQL Statements (DDL) The CREATE statement CREATE TABLE Global temporary tables

previous : next