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
INSERT .. SELECT
Applies to ✅ Open Source Edition ✅ Express Edition ✅ Professional Edition ✅ Enterprise Edition
In some occasions, you may prefer the INSERT SELECT syntax, for instance, when you copy records from one table to another:
create.insertInto(AUTHOR_ARCHIVE) .select(selectFrom(AUTHOR).where(AUTHOR.DECEASED.isTrue())) .execute();
Dialect support
This example using jOOQ:
insertInto(AUTHOR).columns(AUTHOR.ID, AUTHOR.LAST_NAME).select(select(val(100), val("Hesse")))
Translates to the following dialect specific expressions:
Access
INSERT INTO AUTHOR (ID, LAST_NAME) SELECT 100, 'Hesse' FROM ( SELECT count(*) dual FROM MSysResources ) AS dual
ASE, Aurora Postgres, BigQuery, ClickHouse, CockroachDB, DuckDB, Exasol, H2, MariaDB, MySQL, Oracle, Postgres, Redshift, SQLDataWarehouse, SQLServer, SQLite, Snowflake, Trino, Vertica, YugabyteDB
INSERT INTO AUTHOR (ID, LAST_NAME) SELECT 100, 'Hesse'
Aurora MySQL, MemSQL
INSERT INTO AUTHOR (ID, LAST_NAME) SELECT 100, 'Hesse' FROM DUAL
DB2
INSERT INTO AUTHOR (ID, LAST_NAME) SELECT 100, 'Hesse' FROM SYSIBM.DUAL
Derby
INSERT INTO AUTHOR (ID, LAST_NAME) SELECT 100, 'Hesse' FROM SYSIBM.SYSDUMMY1
Firebird
INSERT INTO AUTHOR (ID, LAST_NAME) SELECT 100, 'Hesse' FROM RDB$DATABASE
Hana, Sybase
INSERT INTO AUTHOR (ID, LAST_NAME) SELECT 100, 'Hesse' FROM SYS.DUMMY
HSQLDB
INSERT INTO AUTHOR (ID, LAST_NAME) SELECT 100, 'Hesse' FROM (VALUES (1)) AS dual (dual)
Informix
INSERT INTO AUTHOR (ID, LAST_NAME) SELECT 100, 'Hesse' FROM ( SELECT 1 AS dual FROM systables WHERE (tabid = 1) ) AS dual
Teradata
INSERT INTO AUTHOR (ID, LAST_NAME) SELECT 100, 'Hesse' FROM ( SELECT 1 AS "dual" ) AS "dual"
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!