H2 SQL Database - INSERT if record does not exist

Question

I would like to initialize the H2 database, but I'm not sure if the records exist. If they exist, I do not want to do anything, but if they do not exist, I would like to write the default values.

Something like that:

IF 'number of rows in ACCESSLEVELS' = 0
INSERT INTO ACCESSLEVELS VALUES
    (0, 'admin'),
    (1, 'SEO'),
    (2, 'sales director'),
    (3, 'manager'),
    (4, 'REP')
    ;

Answer 1

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' union
select 1, 'SEO' union
select 2, 'sales director' union
select 3, 'manager' union
select 4, 'REP'
) x where not exists(select * from ACCESSLEVELS);

Answer 2

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

Updates existing rows and inserts rows that do not exist. If no key column is specified, the primary key columns are used to find the row.

Answer 3

MySQL H2. 1.4.197 : INSERT IGNORE INTO table_name VALUES...

:

INSERT IGNOREnot supported in normal mode, you must explicitly enable MySQL compatibility mode by adding ;MODE=MySQLto the URL of your database or by following the instructions SET MODE MySQL.

From the official website :

INSERT IGNOREpartially supported and can be used to skip lines with duplicate keys, if not specified ON DUPLICATE KEY UPDATE.