H2 SQL database - INSERT if the record does not exist

MERGE INTO ACCESSLEVELS   KEY(ID) VALUES (0, 'admin'),  (1, 'SEO'),  (2, 'sales director'),  (3, 'manager'),  (4, 'REP');

Updates existing rows, and insert rows that don't exist. If no key column is specified, the primary key columns are used to find the row.

If you do not name the columns, their values must be provided as defined in the table. If you prefer to name the columns to be more independent from their order in the table definition, or to avoid having to provide values for all columns when that is not necessary or possible:

MERGE INTO ACCESSLEVELS   (ID, LEVELNAME)  KEY(ID) VALUES (0, 'admin'),  (1, 'SEO'),  (2, 'sales director'),  (3, 'manager'),  (4, 'REP');

Note that you must include the key column ("ID" in this example) in the column list as well as in the KEY clause.

The following works for MySQL, PostgreSQL, and the H2 database:

drop table ACCESSLEVELS;create table ACCESSLEVELS(id int, name varchar(255));insert into ACCESSLEVELS select * from (select 0, 'admin' unionselect 1, 'SEO' unionselect 2, 'sales director' unionselect 3, 'manager' unionselect 4, 'REP') x where not exists(select * from ACCESSLEVELS);

To do this you can use MySQL Compatibility Mode in H2 database. Starting from 1.4.197 version it supports the following syntax:INSERT IGNORE INTO table_name VALUES ...

From this pull request:

INSERT IGNORE is not supported in Regular mode, you have to enable MySQL compatibility mode explicitly by appending ;MODE=MySQL to your database URL or by executing SET MODE MySQL statement.

From official site:

INSERT IGNORE is partially supported and may be used to skip rows with duplicate keys if ON DUPLICATE KEY UPDATE is not specified.