Available in versions: Dev (3.21) | Latest (3.20) | 3.19 | 3.18 | 3.17 | 3.16 | 3.15 | 3.14 | 3.13 | 3.12 | 3.11
WHEN NOT MATCHED THEN INSERT
Applies to ✅ Open Source Edition ✅ Express Edition ✅ Professional Edition ✅ Enterprise Edition
When there's no MATCH between SOURCE row and TARGET table, then data derived from the SOURCE row can be inserted into the TARGET table, similar to what INSERT .. SELECT would allow.
Dialect support
This example using jOOQ:
mergeInto(BOOK_TO_BOOK_STORE)
.using(BOOK_TO_BOOK_STORE_STAGING)
.on(BOOK_TO_BOOK_STORE.BOOK_ID.eq(BOOK_TO_BOOK_STORE_STAGING.BOOK_ID)
.and(BOOK_TO_BOOK_STORE.NAME.eq(BOOK_TO_BOOK_STORE_STAGING.NAME)))
.whenNotMatchedThenInsert(
BOOK_TO_BOOK_STORE.BOOK_ID,
BOOK_TO_BOOK_STORE.NAME,
BOOK_TO_BOOK_STORE.STOCK
)
.values(
BOOK_TO_BOOK_STORE_STAGING.BOOK_ID,
BOOK_TO_BOOK_STORE_STAGING.NAME,
BOOK_TO_BOOK_STORE_STAGING.STOCK
)
Translates to the following dialect specific expressions:
DB2, Databricks, Derby, Exasol, Firebird, H2, HSQLDB, Hana, Informix, Postgres, Snowflake, Sybase, Teradata, Vertica
MERGE INTO BOOK_TO_BOOK_STORE USING BOOK_TO_BOOK_STORE_STAGING ON ( BOOK_TO_BOOK_STORE.BOOK_ID = BOOK_TO_BOOK_STORE_STAGING.BOOK_ID AND BOOK_TO_BOOK_STORE.NAME = BOOK_TO_BOOK_STORE_STAGING.NAME ) WHEN NOT MATCHED THEN INSERT (BOOK_ID, NAME, STOCK) VALUES ( BOOK_TO_BOOK_STORE_STAGING.BOOK_ID, BOOK_TO_BOOK_STORE_STAGING.NAME, BOOK_TO_BOOK_STORE_STAGING.STOCK )
Oracle
MERGE INTO BOOK_TO_BOOK_STORE USING BOOK_TO_BOOK_STORE_STAGING ON (( BOOK_TO_BOOK_STORE.BOOK_ID = BOOK_TO_BOOK_STORE_STAGING.BOOK_ID AND BOOK_TO_BOOK_STORE.NAME = BOOK_TO_BOOK_STORE_STAGING.NAME )) WHEN NOT MATCHED THEN INSERT (BOOK_ID, NAME, STOCK) VALUES ( BOOK_TO_BOOK_STORE_STAGING.BOOK_ID, BOOK_TO_BOOK_STORE_STAGING.NAME, BOOK_TO_BOOK_STORE_STAGING.STOCK )
SQLServer
MERGE INTO BOOK_TO_BOOK_STORE USING BOOK_TO_BOOK_STORE_STAGING ON ( BOOK_TO_BOOK_STORE.BOOK_ID = BOOK_TO_BOOK_STORE_STAGING.BOOK_ID AND BOOK_TO_BOOK_STORE.NAME = BOOK_TO_BOOK_STORE_STAGING.NAME ) WHEN NOT MATCHED THEN INSERT (BOOK_ID, NAME, STOCK) VALUES ( BOOK_TO_BOOK_STORE_STAGING.BOOK_ID, BOOK_TO_BOOK_STORE_STAGING.NAME, BOOK_TO_BOOK_STORE_STAGING.STOCK );
ASE, Access, Aurora MySQL, Aurora Postgres, BigQuery, ClickHouse, CockroachDB, DuckDB, MariaDB, MemSQL, MySQL, Redshift, SQLDataWarehouse, SQLite, Trino, YugabyteDB
/* UNSUPPORTED */
Generated with jOOQ 3.21. Translate your own SQL on our website
Feedback
Do you have any feedback about this page? We'd love to hear it!