2 databases on the one H2 instance

H2 supports direct access only to one database at once, but you can create linked tables to tables from other databases.

To create a linked table, you can use a CREATE LINKED TABLE command: https://h2database.com/html/commands.html#create_linked_table

CREATE LINKED TABLE targetTableName('', 'jdbcURL', 'username', 'password', 'sourceTableName');

You can also link the whole schema with LINK_SCHEMA function: https://h2database.com/html/functions.html#link_schema

CALL LINK_SCHEMA('targetSchemaName', '', 'jdbcURL', 'username', 'password', 'sourceSchemaName');

Note that format of fully-qualified table name in H2 (and in the SQL Standard) is catalogName.schemaName.tableName. H2 supports only one catalog (and its name is the same as a name of database) and you can’t define additional catalogs. Non-standard -syntax with [identifier] is not accepted by H2 unless you use a MSSQLServer compatibility mode. In this mode you can use that syntax, but you can’t have different names of catalogs anyway, so if they are fixed in your application, you have a problem.

Actually H2 can ignore the specified name of catalog if IGNORE_CATALOGS setting is set to TRUE: https://h2database.com/html/commands.html#set_ignore_catalogs

SET IGNORE_CATALOGS TRUE;

But if combinations of schema and table name aren’t unique in your original configuration, there is nothing to do with H2. You can’t create different tables with the same schema and table name in H2 in any way.

CLICK HERE to find out more related problems solutions.

Leave a Comment

Your email address will not be published.

Scroll to Top