Local H2 datasets

AS H2 writes a result into a table of a local H2 database: a working copy you can query with SQL, refresh later, and keep longer than the source keeps its own rows. The syntax common to every destination is in Exporting data.

Writing to a local H2 database (AS H2)

<name> (before the dot): the target H2 database. If it already names a connection, that connection is reused (it must be H2; DUMP refuses to touch a connection of any other type). Otherwise a new H2 database is created automatically and registered under that name. <table> (after the dot): the table to write into that database.

A newly created connection is registered as a standalone connection (no Database Group) in the built-in LOCAL environment. Both can be changed afterward from CONFIG.

How times and time zones are kept is described in Reference details.

Example: a local working dataset

Run an expensive or remote query once, keep the result locally, then work on the copy.

-- 1. A useful query on the remote system
SELECT ID, NAME, COUNTRYCODE, POPULATION FROM CITY WHERE POPULATION > 1000000;

-- 2. Keep the displayed result as a local H2 table (WORKCOPY is created on first use)
DUMP / TO WORKCOPY.BIGCITIES AS H2;

-- 3. Query the local copy
CONNECT WORKCOPY;
SELECT COUNTRYCODE, COUNT(*) AS CITIES, SUM(POPULATION) AS TOTAL FROM BIGCITIES GROUP BY COUNTRYCODE ORDER BY TOTAL DESC;

The copy is a snapshot: run a DUMP (<query>) TO ... AS H2 again (with MODE OVERWRITE, MODE APPEND, MODE APPEND KEY(...) or MODE MERGE KEY(...)) to refresh it.

The Connections Definition File is never a destination

The Connections Definition File ($CDF) is a protected BroadSQL system database. DUMP, PULL and EXPORT refuse to write into it, whatever the mode:

DUMP (SELECT 1 AS X) TO $CDF.CONNECTIONS AS H2;

BroadSQL refuses, and writes nothing:

DUMP refused: this destination is the BroadSQL Connections Definition File ($CDF), a protected BroadSQL system database. It cannot be used as a write destination for DUMP, PULL or EXPORT. Nothing was written. Choose another destination.

The refusal also applies to any other connection that opens the same database file, whatever its name, to a copy of the CDF, and in CDF maintenance mode. Connections are still managed as usual with CONFIG and the connection commands. See The configuration database (CDF).

Provenance (BROADSQL_PULL_AUDIT)

Every successful AS H2 export also records one row in BROADSQL_PULL_AUDIT, a table BroadSQL maintains inside the destination database: where the table came from, when it was last refreshed, from what query and connection, and how many rows. One row per execution, never overwritten:

SELECT * FROM BROADSQL_PULL_AUDIT
WHERE TARGET_TABLE = 'CITY'
ORDER BY EXECUTED_AT DESC;

BROADSQL_PULL_AUDIT is a reserved name: it cannot itself be used as a destination table.

MODE OVERWRITE

MODE APPEND

MODE APPEND adds the rows of the result to an existing table. One rule decides whether it can:

the table has the same number of columns as the result, with the same names, in the same order.

When it does, every row the result returns is inserted. Nothing else is compared:

• The query itself is never examined. A join, several joins, a subquery, a WITH query, a UNION, functions, GROUP BY or syntax specific to your database are all fine: only the columns of the final result count.
• Data types are not compared. Each value is inserted into the table's column at the same position; a value the column cannot hold is reported by the database, and the table is then left unchanged.
• Column names are compared without regard to case: id matches ID. Columns are never reordered or matched by name in another position.
• A result with no rows appends nothing and is not an error.
• NULL values are appended as NULL.
• Rows are never compared with the rows already in the table: running the same DUMP twice adds the rows twice.
• When the table does not exist yet, it is created from the result, then later runs append to it.
• Every source can be appended: a table, a query in parentheses, /, LIB or @, API RESULT.

When the columns differ, nothing is written and the error names the first difference:

APPEND refused: destination has 3 columns but query returns 4. APPEND requires the same columns, with the same names, in the same order.

When a column has another name, the error names it:

APPEND refused: column 2 differs.
Query: CITY_NAME
Destination: CITY
APPEND requires the same columns, with the same names, in the same order.

Alias the query's columns (CI.NAME AS CITY) to match the table. MODE APPEND works the same way for a worksheet of an XLSX or ODS file (see Files and spreadsheets).

MODE APPEND KEY(<column>) [FORCE]

MODE APPEND KEY adds only the rows that are new since the last run. It needs a SQL source (a table or a query in parentheses): it filters that query itself.

How it behaves:

• First run (the target table does not exist yet): every row the query returns is loaded.
• Later runs: BroadSQL reads the current maximum value of <column> in the target table, then only fetches and inserts source rows whose <column> is strictly greater. No row already in the target is ever updated or deleted, and running again with no new source rows inserts nothing.
• <column> must be a single column of a numeric or date/time type.
• The source's own WHERE condition keeps its meaning: the key filter is added to the whole condition, WHERE (<condition>) AND <column> > <maximum>, so a condition with OR never brings back rows already in the target.
• <column> may name a column alias of the select list (CI.ID AS CITY_ID, then KEY(CITY_ID)): the filter then uses the aliased expression (O.ID), since a WHERE clause cannot refer to an alias. In a JOIN, an unaliased key such as C.ID is filtered as C.ID, never as an ambiguous ID.
• The source query may use JOIN (any kind), but not a comma-joined or derived (subquery) FROM, nor GROUP BY, HAVING, ORDER BY, UNION, LIMIT, OFFSET or FETCH.
• Before inserting, BroadSQL checks whether <column> could ever be NULL for this query, since a NULL key would be skipped by every later run. A key confirmed as nullable is always refused. When BroadSQL cannot tell (a computed key such as COALESCE(...)), it refuses too; add FORCE after KEY(<column>) once you have checked yourself that the key can never be NULL.
• An outer join always prints a caution note: depending on the database, BroadSQL's check may not detect a NULL key produced by an unmatched row.

Typical use: mirror a table whose old rows are purged at the source into a local H2 database with a longer retention window, kept current by running the same command again:

DUMP CITY TO ARCHIVE.CITY AS H2 MODE APPEND KEY(ID);

MODE MERGE KEY(<column>[, <column> ...])

MODE MERGE keeps a local table up to date with a result, row by row, identified by a key: one or more columns of the result.

DUMP (SELECT CODE, TRIM(NAME) AS NAME, POPULATION FROM COUNTRY) TO WORKCOPY.COUNTRY_FIGURES AS H2 MODE MERGE KEY(CODE);

After one population was changed in the copy, the next run puts the source value back and counts the rest:

WORLD> DUMP (SELECT CODE, TRIM(NAME) AS NAME, POPULATION FROM COUNTRY) TO WORKCOPY.COUNTRY_FIGURES AS H2 MODE MERGE KEY(CODE);
MERGE into WORKCOPY.COUNTRY_FIGURES: 254 source row(s), 0 inserted, 1 updated, 253 unchanged.
Completed: 2026-10-18 10:15:47 +02:00 (0:00:00).

The DUMP tutorial shows these runs step by step.

How it behaves:

• The whole result is read at every run. Without change tracking in the source, every row has to be compared; only the new and changed rows are written.
• Every source works: a table, a query in parentheses (a join or an outer join included: the query is never changed), /, LIB or @, API RESULT.
• The key is one or more columns of the result: KEY(COUNTRYCODE, LANGUAGE) for a composite key. Names are compared without regard to case.
• The values are compared after conversion to the table's column types. A DECIMAL(10,2) column rounds 3.004 to 3.00 (half up: 3.005 becomes 3.01), so 3.004 is unchanged against 3.00. Text is compared exactly: abc and ABC differ, and so do abc and abc (trailing space). A change from or to NULL is an update; NULL against NULL is unchanged.
• A table that does not exist yet is created from the result, with a unique index on the key, and every row is inserted. A table created by MODE OVERWRITE or MODE APPEND has no index: merging into it works, more slowly. Create an index on the key columns of a large table to make every later MERGE faster.
• All or nothing. The changes are made in one transaction, after every check: after a refusal, a failure or Ctrl+C, the table is exactly as it was. A table the MERGE had to create is removed again if the MERGE then fails.
• Every successful run records one row in BROADSQL_PULL_AUDIT, with PULL_MODE MERGE and, as its row count, the rows inserted plus the rows updated.

MERGE refuses, writes nothing and says why when:

MODE MERGE is for AS H2 only. To keep a spreadsheet up to date, merge into a local H2 table, then DUMP that table AS XLSX.

Disk space. The rows are first copied into a temporary table of the destination database: during a MERGE, its file grows by roughly the size of the result (several times that size when the table has no index on the key). H2 reuses that space afterwards; the file may stay larger until H2 compacts it.

Example: refresh a working copy every morning

The first run creates WORKCOPY and the table; each later run adds only the new rows:

DUMP (SELECT ID, TRIM(NAME) AS NAME, COUNTRYCODE, POPULATION FROM CITY) TO WORKCOPY.CITY AS H2 MODE APPEND KEY(ID);
CONNECT WORKCOPY;
SELECT TARGET_TABLE, EXECUTED_AT FROM BROADSQL_PULL_AUDIT ORDER BY EXECUTED_AT DESC;

The last query lists every refresh recorded in BROADSQL_PULL_AUDIT.

Related pages

• Import / Export overview
• Exporting data: syntax, sources, unsupported forms.
• Connections, Database Groups and Environments: the LOCAL environment and standalone connections.
• Command reference: DUMP, CONNECT.

Found something wrong or unclear on this page? Tell us, with the page address.