How do I export tables and query results with DUMP? A step-by-step tutorial

Applies to BroadSQL 5.5.0 · reviewed October 18, 2026

What you'll accomplish

Getting data out of a database is an everyday task: a CSV file for another tool, a worksheet for a colleague, a table to paste into a ticket, a local copy to query without touching the source. BroadSQL does all of it with one command, DUMP, whose few words (what to export, where, in which format, how to write it) combine in the same way for every destination.

You will work through the uses of DUMP on the WORLD sample, from a first CSV file to a local database that you refresh by key. Each step adds one idea and shows the result. At the end you know which source, format and mode to choose for a task, and the mistakes to avoid. The DUMP reference page lists every option in full.

Short answer

DUMP <source> TO <destination> AS <format> [MODE <mode>]; where the source is a table, a query in parentheses or / (the result on screen). Start with DUMP COUNTRY TO countries AS CSV;.

Before you start

Connect to the sample with CONNECT WORLD;. DUMP writes its files to the export folder, the DefaultFolder setting of BroadSQL.ini (c:\temp\ by default on Windows; the Linux template names a folder for you to adapt). The messages below show c:\temp\; yours show your folder. Every step only reads WORLD: the files and the local database it writes are yours, and Clean up says how to remove them.

1. Export a whole table

The simplest source is a table name. This command writes every row of CURRENCY to a CSV file named currencies.csv:

DUMP CURRENCY TO currencies AS CSV;

BroadSQL says how many rows it wrote and where, then when the export finished and how long it took:

166 row(s) exported to c:\temp\currencies.csv.
Completed: 2026-10-18 10:02:11 +02:00 (0:00:00).

The first line of the file holds the column names, separated by semicolons (the CsvSeparator setting; COMMA is the other choice). Without TO, the file would be named after the table; without AS, the format would be the DefaultFileFormat setting (ODS unless you changed it), so name both when the result matters.

2. Export a query

To choose the columns and the rows, export a query: write it in parentheses as the source. This command exports the countries of Oceania with at least a million inhabitants, largest first:

DUMP (SELECT CODE, TRIM(NAME) AS NAME, POPULATION FROM COUNTRY WHERE CONTINENT = 'Oceania' AND POPULATION >= 1000000 ORDER BY POPULATION DESC) TO oceania AS CSV;

The query runs now, and every row it returns is written, whatever the MaxRowsOnScreen limit of the screen. The file oceania.csv contains:

CODE;NAME;POPULATION
AUS;Australia;18886000
PNG;Papua New Guinea;4807000
NZL;New Zealand;3862000

Why TRIM(NAME) AS NAME? Most text columns of WORLD are fixed length (CHAR(52) for COUNTRY.NAME): the database pads each value with spaces up to that length, and DUMP writes the values as the database returns them. TRIM removes the padding, and AS NAME keeps the column's name in the file. Your own tables may not need it.

3. Export the result you are looking at

Often you run a query, look at the result, and then decide to keep it. / as the source exports the result on screen without running the query again. Run a query first:

SELECT TRIM(NAME) AS CITY, POPULATION FROM CITY WHERE COUNTRYCODE = 'FRA' ORDER BY POPULATION DESC LIMIT 5;

Then export what you saw as a Markdown table, ready to paste into a ticket, a wiki page or a pull request:

DUMP / TO france_top5 AS MD;

The file france_top5.md contains:

| CITY | POPULATION |
| --- | --- |
| Paris | 2125246 |
| Marseille | 798430 |
| Lyon | 445452 |
| Toulouse | 390350 |
| Nice | 342738 |

DUMP / exports only a result that was displayed in full. A result cut at the MaxRowsOnScreen limit (100 rows by default) is refused: export it with DUMP (<query>) instead, which runs the query and writes every row.

4. Choose a text format

The same command writes other formats: change the word after AS. Each file is rewritten completely at every run.

For example, the five French cities of step 3 as JSON, then as an HTML table:

DUMP / TO france_top5 AS JSON;
DUMP / TO france_top5 AS HTML;

5. Write a spreadsheet, one worksheet per export

For a spreadsheet, the destination names the file and the worksheet, separated by a dot: world_report.CONTINENTS is the worksheet CONTINENTS of world_report.xlsx. This command writes the number of countries and the population of each continent:

DUMP (SELECT TRIM(CONTINENT) AS CONTINENT, COUNT(*) AS COUNTRIES, SUM(POPULATION) AS POPULATION FROM COUNTRY GROUP BY CONTINENT ORDER BY POPULATION DESC) TO world_report.CONTINENTS AS XLSX;

A second command adds another worksheet to the same file, the ten languages that are official in the most countries. The first worksheet is kept:

DUMP (SELECT TRIM(LANGUAGE) AS LANGUAGE, COUNT(*) AS COUNTRIES FROM COUNTRYLANGUAGE WHERE ISOFFICIAL = 'T' GROUP BY LANGUAGE ORDER BY COUNTRIES DESC, LANGUAGE LIMIT 10) TO world_report.LANGUAGES AS XLSX;

Each export reports its worksheet and file:

7 row(s) exported to tab 'CONTINENTS' of c:\temp\world_report.xlsx.
Completed: 2026-10-18 10:05:40 +02:00 (0:00:00).

10 row(s) exported to tab 'LANGUAGES' of c:\temp\world_report.xlsx.
Completed: 2026-10-18 10:05:52 +02:00 (0:00:00).

world_report.xlsx now holds three worksheets: CONTINENTS, LANGUAGES, and QUERIES, which BroadSQL keeps up to date with the source, the date and the row count of each worksheet. Running the first command again replaces CONTINENTS only; a worksheet you added by hand is kept too. Numbers stay numbers in the spreadsheet. Add OPEN at the end to open the file when it is written, and use AS ODS for an OpenDocument file.

6. Add rows to a worksheet over time

A worksheet can also grow: MODE APPEND adds the rows of the result below the existing ones instead of replacing the worksheet. To keep a history, put the date in the result. This command adds today's number of countries per continent to the worksheet HISTORY:

DUMP (SELECT CURRENT_DATE AS SNAPSHOT_DATE, TRIM(CONTINENT) AS CONTINENT, COUNT(*) AS COUNTRIES FROM COUNTRY GROUP BY CONTINENT) TO world_report.HISTORY AS XLSX MODE APPEND;

The first run creates the worksheet. Each later run, next week for example, adds seven rows below the last one:

7 row(s) exported to tab 'HISTORY' of c:\temp\world_report.xlsx (MODE APPEND).
Completed: 2026-10-18 10:07:03 +02:00 (0:00:00).

MODE APPEND writes only when the worksheet's header row names the same columns, in the same order, as the result (letter case aside). Otherwise nothing is written and the error names the first difference. It never compares rows: running the same command twice on the same day adds the same seven rows twice. A spreadsheet has no key, so MODE APPEND KEY(...) and MODE MERGE KEY(...) are refused for XLSX and ODS: they belong to the next steps.

7. Keep a local copy in an H2 database

AS H2 writes the result into a table of a local H2 database, which you can query with SQL like any other database. The destination is <connection>.<table>. This command copies the cities of at least a million inhabitants into the table BIG_CITIES of a database named WORKCOPY:

DUMP (SELECT ID, TRIM(NAME) AS NAME, COUNTRYCODE, POPULATION FROM CITY WHERE POPULATION >= 1000000) TO WORKCOPY.BIG_CITIES AS H2;

WORKCOPY does not exist yet, so BroadSQL first creates it: a database file WORKCOPY.mv.db in the export folder, registered as a standalone connection in the LOCAL environment, and says so in one line. Then it reports the table:

239 row(s) exported to WORKCOPY.BIG_CITIES (table dropped and recreated).
Completed: 2026-10-18 10:09:15 +02:00 (0:00:00).

Query the copy like any database. Open it and count the large cities per country:

CONNECT WORKCOPY;
SELECT COUNTRYCODE, COUNT(*) AS CITIES FROM BIG_CITIES GROUP BY COUNTRYCODE ORDER BY CITIES DESC, COUNTRYCODE LIMIT 5;

The copy answers without the source being open:

WORKCOPY> SELECT COUNTRYCODE, COUNT(*) AS CITIES FROM BIG_CITIES GROUP BY COUNTRYCODE ORDER BY CITIES DESC, COUNTRYCODE LIMIT 5;
|-----------|--------------------|
|COUNTRYCODE|CITIES              |
|-----------|--------------------|
|CHN        |35                  |
|IND        |18                  |
|BRA        |13                  |
|RUS        |12                  |
|JPN        |11                  |
|-----------|--------------------|

5 rows fetched in 3 ms.

Every AS H2 export also adds one row to BROADSQL_PULL_AUDIT, a table BroadSQL keeps in the same database: the source query, the source connection, the time and the row count. SELECT * FROM BROADSQL_PULL_AUDIT; shows where each table came from. Go back to the sample with CONNECT WORLD; before the next step.

8. Refresh a local copy: the four modes

A copy is a snapshot. To refresh it, run a DUMP to the same table again, with the mode that fits the data.

Add only the new rows: APPEND KEY

CITY has an increasing key, ID. The first run copies the whole table:

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

The table does not exist yet, so BroadSQL creates it and copies every row:

4081 row(s) exported to WORKCOPY.CITY (table created).
Completed: 2026-10-18 10:12:30 +02:00 (0:00:00).

The next run reads the largest ID of the copy (4090), then fetches only the source rows whose ID is greater. With no new city in WORLD, it copies nothing; after a new city is added at the source, it copies that one row. A row already copied is never updated, so APPEND KEY suits data that does not change once written: logs, events, orders.

Insert and update by key: MERGE KEY

When rows change at the source, MERGE compares every row of the result with the copy, by key. This command keeps a copy of a few figures per country, identified by CODE:

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

The first run creates the table, with a unique index on the key, and inserts every row:

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

To see an update without changing WORLD, change the copy instead: open WORKCOPY, set a wrong population for France, then go back to the sample:

CONNECT WORKCOPY;
UPDATE COUNTRY_FIGURES SET POPULATION = 0 WHERE CODE = 'FRA';
CONNECT WORLD;

Run the same DUMP ... MODE MERGE KEY(CODE) command again. It finds the one row that differs from the source and puts the source value back:

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

A key can have several columns. COUNTRYLANGUAGE is identified by a country and a language:

DUMP (SELECT COUNTRYCODE, TRIM(LANGUAGE) AS LANGUAGE, ISOFFICIAL, PERCENTAGE FROM COUNTRYLANGUAGE) TO WORKCOPY.LANGUAGES AS H2 MODE MERGE KEY(COUNTRYCODE, LANGUAGE);

What KEY means

Both work for AS H2 only, and both write nothing at all when they refuse: the message says why.

9. Export a Script's result

A saved Script of the Scripts Library is a source too. DUMP LIB runs it as LIB RUN would and writes its final result, only when the Script ends with the status SUCCESS. With the Script of Save a query and run it by name:

DUMP LIB countries/largest.sql continent='Asia' TO asia_largest AS XLSX;

See Exporting Script results for the details.

Where each destination differs

Typical mistakes and precautions

• A dot in a flat file name: TO report.data AS CSV is refused, since only spreadsheets and databases have a part after the dot. Write TO report_data AS CSV.
• MODE with a flat file: refused; a CSV, JSON or Markdown file is always rewritten. To build a history, use a worksheet (XLSX ... MODE APPEND) or an H2 table.
• A file open in Excel: Windows does not let BroadSQL replace it. The export fails, and the file is unchanged. Close it and run the command again.
• Forgetting AS: the format then comes from DefaultFileFormat. Name the format in Scripts, so that a change of setting does not change the files.
• Appending twice: MODE APPEND never compares rows. Run it once per period, or use MERGE KEY on an H2 table.
• Expecting MERGE to delete: a row removed at the source stays in the copy. To mirror deletions, use MODE OVERWRITE.
• A large spreadsheet: a worksheet holds at most 1048575 rows below its header. Use CSV beyond that.
• The configuration database: AS H2 refuses $CDF, the database that holds your connections.

Clean up

The steps wrote currencies.csv, oceania.csv, the three france_top5 files and world_report.xlsx in the export folder, plus the local database WORKCOPY. Delete the files when you no longer need them. For WORKCOPY, first DEL CONNECTION WORKCOPY; (the connection becomes inactive), then delete WORKCOPY.mv.db from the export folder while BroadSQL is closed. WORLD itself was only read.

Related

• DUMP reference: every source, format, mode and message
• Build a weekly Excel report with one tab per query
• Keep a local copy of a table up to date
• Files and spreadsheets and Local H2 datasets

Commands used

• DUMP
• CONNECT
• DEL CONNECTION

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