H2 SQL database - INSERT if the record does not exist

Question

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

Something like this:

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

score 25 · Answer 1 · edited Oct 15 '21 at 22:44

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.

I know this function, but I don't want to update the records if they exist. — szedjani, Nov 04 '13 at 13:39
try write user function where you use "select count(ID) into x from ..." and then compare result... — MartinB, Nov 04 '13 at 14:48

score 13 · Accepted Answer · answered Nov 04 '13 at 14:02

13

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);

answered Nov 04 '13 at 14:02

Thomas Mueller

48,905
14
116
132

2

Can you explain this syntax a little bit? Having a hard time deciphering it – IcedDante Mar 19 '14 at 18:49
It's a "insert into ... select" statement, which is typically used to insert multiple rows from a query into a table. Now instead of a simple "select * from tableName", I have used a "union" query with hardcoded values. Then the "where not exists" ensures this rows are only inserted if the table "accesslevels" is empty. – Thomas Mueller Mar 19 '14 at 19:03

score 2 · Answer 3 · answered Jan 09 '19 at 18:09

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.

score 0 · Answer 4 · answered Mar 30 '20 at 07:03

Here is another way:

CREATE TABLE target (C1 VARCHAR(255), C2 VARCHAR(255));

MERGE INTO target AS T USING (SELECT 'foo' C1, 'bar') AS S ON T.C1=S.C1
WHEN NOT MATCHED THEN
    INSERT VALUES('foo', 'bar')

When a row in S matches one or more rows in T, do nothing. But when a row in S is not matched, insert it. See "MERGE USING" for more details:

https://www.h2database.com/html/commands.html#merge_using

H2 SQL database - INSERT if the record does not exist

4 Answers4

Linked