从SELECT创建
适用于 ✅ 开源版 ✅ 专业版 ✅ 企业版
有时，从 SELECT 语句 创建表非常有用，可以复制源表的数据类型和数据。
// 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();
方言支持
此示例使用 jOOQ
createTable("book_archive")
.as(select(BOOK.ID, BOOK.TITLE).from(BOOK))
.withNoData()
翻译成以下特定方言的表达式
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
Databricks
CREATE TABLE book_archive TBLPROPERTIES( 'delta.columnMapping.mode' = 'name', 'delta.feature.allowColumnDefaults' = 'supported' ) 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 */
使用 jOOQ 3.21 生成。早期 jOOQ 版本的支持可能有所不同。 在我们的网站上翻译您自己的 SQL
反馈
您对此页面有任何反馈吗？ 我们很乐意听取您的意见！