On this page

Documentation / How do I…: How do I keep a local copy of a production table up to date?

How do I keep a local copy of a production table up to date?

Applies to BroadSQL 5.5.0 · reviewed October 18, 2026

What you'll accomplish

You query the same reference table many times a day: to join it with other data, to look something up during an incident, or to work while the source is slow or far away. Querying production each time adds load and risk, and a one-off copy goes stale.

You will keep a local H2 copy of the table and refresh it with one command that inserts the new rows, updates the changed ones and leaves the others alone, then reports how many of each. Every refresh is recorded in the copy, so you can always tell how fresh it is.

Short answer

Run DUMP <table> TO <local>.<table> AS H2 MODE MERGE KEY(<key>); whenever you want a fresh copy: the first run creates the copy, every later run merges the changes.

Example

Connected to WORLD, copy the countries into a local database WORKCOPY, identified by their CODE:

Type at the BroadSQL prompt

DUMP COUNTRY TO WORKCOPY.COUNTRY AS H2 MODE MERGE KEY(CODE);

The first run creates the local database WORKCOPY in the export folder and the table, with a unique index on the key, then reports:

What BroadSQL prints

MERGE into WORKCOPY.COUNTRY (table created): 254 source row(s), 254 inserted, 0 updated, 0 unchanged.
Completed: 2026-10-18 10:30:05 +02:00 (0:00:00).

Run the same command tomorrow. If nothing changed at the source, nothing is written and every row is counted as unchanged:

What BroadSQL prints

MERGE into WORKCOPY.COUNTRY: 254 source row(s), 0 inserted, 0 updated, 254 unchanged.
Completed: 2026-10-19 08:45:12 +02:00 (0:00:00).

When rows change at the source, the counts say how many were added and updated. To check when the copy was last refreshed, open it and read the record BroadSQL keeps there:

Type at the BroadSQL prompt

CONNECT WORKCOPY;
SELECT TARGET_TABLE, EXECUTED_AT FROM BROADSQL_PULL_AUDIT ORDER BY EXECUTED_AT DESC;

BROADSQL_PULL_AUDIT holds one row per refresh: source, query, connection, time and row count.

Variations

Only add new rows (a log, an event table): MODE APPEND KEY(ID) reads the largest key of the copy and fetches only the rows above it, so it reads much less than MERGE. Rows already copied are never updated: DUMP CITY TO WORKCOPY.CITY AS H2 MODE APPEND KEY(ID);.
Some columns, or a join: merge a query, DUMP (SELECT CODE, TRIM(NAME) AS NAME, POPULATION FROM COUNTRY) TO WORKCOPY.COUNTRY_FIGURES AS H2 MODE MERGE KEY(CODE);.
A key of several columns: MODE MERGE KEY(COUNTRYCODE, LANGUAGE) for COUNTRYLANGUAGE.
Every morning: keep the commands in a Script and run it with @ at the start of the day.
A spreadsheet that stays current: after the merge, DUMP COUNTRY TO report AS XLSX; from WORKCOPY (see Build a weekly Excel report).

The DUMP tutorial shows an update happening, step by step.

Watch out

MERGE never deletes: a row removed at the source stays in the copy. Use MODE OVERWRITE for an exact mirror.
MERGE refuses, and writes nothing, when a key is NULL or appears twice in the source; the message says which.
It is all or nothing: after a refusal, a failure or Ctrl+C, the table is exactly as it was.
The whole source result is read at every run. For a large table whose rows never change, prefer APPEND KEY.
The copy is yours to change, but a change you make in it is overwritten by the next merge for that key.

Clean up

DEL CONNECTION WORKCOPY; makes the connection inactive; then delete WORKCOPY.mv.db from the export folder while BroadSQL is closed.

Commands used

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