Available in versions: Dev (3.20) | Latest (3.19) | 3.18 | 3.17 | 3.16 | 3.15 | 3.14 | 3.13 | 3.12 | 3.11 | 3.10
From a SELECT
Applies to ✅ Open Source Edition ✅ Express Edition ✅ Professional Edition ✅ Enterprise Edition
Occasionally, creating a table from a SELECT statement is very useful, copying the source table's data types and data.
// Create a new table from a source SELECT statement
create.createTable("book_archive")
.as(select(BOOK.ID, BOOK.TITLE).from(BOOK))
.execute();
// Create a new table from a source SELECT statement and specify that data should be included, explicitly
create.createTable("book_archive")
.as(select(BOOK.ID, BOOK.TITLE).from(BOOK))
.withData()
.execute();
// Create a new table from a source SELECT statement and specify that data should be excluded, explicitly
create.createTable("book_archive")
.as(select(BOOK.ID, BOOK.TITLE).from(BOOK))
.withNoData()
.execute();
Dialect support
This example using jOOQ:
createTable("book_archive")
.as(select(BOOK.ID, BOOK.TITLE).from(BOOK))
.withNoData()
Translates to the following dialect specific expressions:
ASE, Access, SQLDataWarehouse, SQLServer
SELECT BOOK.ID, BOOK.TITLE INTO book_archive FROM BOOK WHERE 1 = 0
Aurora MySQL, MemSQL, Oracle, Redshift, SQLite, Vertica
CREATE TABLE book_archive AS SELECT BOOK.ID, BOOK.TITLE FROM BOOK WHERE 1 = 0
Aurora Postgres, Derby, Exasol, Postgres, YugabyteDB
CREATE TABLE book_archive AS SELECT BOOK.ID, BOOK.TITLE FROM BOOK WITH NO DATA
BigQuery
CREATE TABLE book_archive AS ( SELECT BOOK.ID, BOOK.TITLE FROM BOOK WHERE FALSE )
CockroachDB, DuckDB, H2, MariaDB, MySQL, Snowflake
CREATE TABLE book_archive AS SELECT BOOK.ID, BOOK.TITLE FROM BOOK WHERE FALSE
DB2
CREATE TABLE book_archive AS ( SELECT BOOK.ID, BOOK.TITLE FROM BOOK ) WITH NO DATA
HSQLDB, Hana
CREATE TABLE book_archive AS ( SELECT BOOK.ID, BOOK.TITLE FROM BOOK ) WITH NO DATA
Teradata
CREATE TABLE book_archive AS ( SELECT BOOK.ID, BOOK.TITLE FROM BOOK WHERE 1 = 0 ) WITH DATA
ClickHouse, Firebird, Informix, Sybase, Trino
/* UNSUPPORTED */
Generated with jOOQ 3.20. Translate your own SQL on our website
Feedback
Do you have any feedback about this page? We'd love to hear it!