BroadSQL Download
On this page
Documentation / Commands / DUMP

DUMP

Core command

Exports a table, a query result or a Script result to a file or to a table of a local H2 database.

Syntax

DUMP <source> [TO <destination>] [AS <format>] [MODE <mode>] [OPEN]

Quick start

DUMP CUSTOMER;

Writes every row of CUSTOMER to the export folder, in the format of the DefaultFileFormat setting.

DUMP CUSTOMER TO customers AS CSV;

Writes customers.csv to the export folder.

Arguments

ArgumentRequiredValuesDefault
<source>YesA table name, / (the previous result), (<query>), LIB <script>, @<script> or API RESULT.None
TO <destination>No. Required for AS H2.A file name. <file>.<worksheet> for XLSX and ODS. <connection>.<table> for H2.Named after the table or the Script; results otherwise
AS <format>NoCSV, TEXT, JSON, XLSX, ODS, H2, MD, HTMLThe DefaultFileFormat setting
MODE <mode>No. XLSX, ODS and H2 only.OVERWRITE, APPEND. H2 also: APPEND KEY(<column>) [FORCE] and MERGE KEY(<column>[, <column> ...])OVERWRITE
OPENNo. XLSX and ODS only.Opens the file after the exportNot opened

Examples

The previous result, as a text file
SELECT * FROM CUSTOMER WHERE COUNTRY = 'FR';
DUMP / TO customers AS TEXT;
A query into a named worksheet
DUMP (SELECT * FROM CUSTOMER WHERE COUNTRY = 'FR') TO customers.DATA AS XLSX;
A Scripts Library Script, with an argument
DUMP LIB monthly.bsql region='EU' TO revenue_eu AS CSV;
A variable passed to the query as a value
LET c = 'FR';
DUMP (SELECT * FROM CUSTOMER WHERE COUNTRY = ${c}) TO fr AS CSV;
Rows added to an existing worksheet
DUMP / TO report.HISTORY AS XLSX MODE APPEND;
A local H2 copy kept up to date by key
DUMP CUSTOMER TO WORKCOPY.CUSTOMER AS H2 MODE MERGE KEY(ID);

Example session

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).
Illustrative output: the values come from the WORLD sample and the practice connections of the how-to on QA and production copies of WORLD, and the layout from this release; it was not captured from a BroadSQL run, and your timings will differ.
WORLD> DUMP CURRENCY TO REPORT AS XLSX;
166 row(s) exported to tab 'DATA' of c:\temp\REPORT.xlsx.
Completed: 2026-10-18 10:05:40 +02:00 (0:00:01).
WORLD> DUMP (SELECT TRIM(CONTINENT) AS CONTINENT, COUNT(*) AS COUNTRIES FROM COUNTRY GROUP BY CONTINENT) TO REPORT.BY_CONTINENT AS XLSX;
7 row(s) exported to tab 'BY_CONTINENT' of c:\temp\REPORT.xlsx.
Completed: 2026-10-18 10:05:45 +02:00 (0:00:00).
Illustrative output: the values come from the WORLD sample and the practice connections of the how-to on QA and production copies of WORLD, and the layout from this release; it was not captured from a BroadSQL run, and your timings will differ.
SELECT CODE, TRIM(NAME) AS NAME FROM COUNTRY WHERE REGION = 'Baltic Countries' ORDER BY CODE;
DUMP / TO baltic AS MD;
| CODE | NAME |
| --- | --- |
| EST | Estonia |
| LTU | Lithuania |
| LVA | Latvia |
Illustrative output: the values come from the WORLD sample and the practice connections of the how-to on QA and production copies of WORLD, and the layout from this release; it was not captured from a BroadSQL run, and your timings will differ.

Details

PULL is a compatibility alias of DUMP. DUMP writes data or results to a file or to a table of a local H2 database; it is not a database backup.

Sources

  • CUSTOMER, or "Sales Data" for a name with spaces or other special characters: every row of the table or view, found as DESCR finds it.
  • /: the previous result, exactly as it was displayed. The query is not run again. A result whose display stopped at the MaxRowsOnScreen limit is refused: export it with DUMP (<query>), which runs the query and exports every row.
  • (<query>): the query, run now. Its ${name} variables are passed to the database as typed values. Only the query in parentheses is read for variables: TO, AS and the rest of the command are taken as written.
  • LIB <script> [name=value ...] or @<script>: the final tabular result of a Scripts Library Script, run as LIB RUN runs it, with the same named arguments, without displaying its results. The result is written only when the Script ends with the status SUCCESS, and only when it produces a tabular result; a LET query of the Script is never the exported result.
  • API RESULT: the result of the last RUN.

Formats and files

  • CSV: comma or semicolon separated (the CsvSeparator setting), standard quoting.
  • TEXT: UTF-8, tab separated. TXT is accepted as TEXT.
  • JSON: an array of objects.
  • XLSX and ODS: the worksheet DATA, or the worksheet named after the file (TO report.MONTHLY). The other worksheets of an existing file are kept, and a QUERIES worksheet records the source of each worksheet.
  • H2: a table of a local H2 database, TO <connection>.<table>. A connection that does not exist yet is created in the export folder.
  • MD and HTML: a table.

Without AS, the DefaultFileFormat setting applies. Files are written to the export folder (DefaultFolder). Without TO, the file is named after the table, after the Script, or after DefaultExtFileName (results) for /, a query and API RESULT.

Write modes

  • MODE OVERWRITE, the default: replaces the table or the worksheet.
  • MODE APPEND (H2, XLSX, ODS): adds the rows of the result to the existing table or worksheet when it has the same number of columns, with the same names, in the same order as the result (names compared without regard to case). The data types are not compared and the query is not examined, so any query can be appended. A destination that does not exist yet is created. When the columns differ, nothing is written and the error names the difference. NULL values are appended as NULL (an empty cell in a worksheet).
  • MODE APPEND KEY(<column>) [FORCE] (H2): adds only the rows whose key is greater than the table's current maximum, see PULL.
  • MODE MERGE KEY(<column>[, <column> ...]) (H2): the rows whose key is not in the table are inserted, the rows whose key is there and whose other columns differ are updated, and the others are only counted. A row of the table whose key is not in the result is kept as it is: MERGE never deletes. The summary gives the counts, for example MERGE into WORKCOPY.CUSTOMER: 10000 source row(s), 120 inserted, 37 updated, 9843 unchanged.

MERGE in detail

  • The whole result is read every time: without change tracking in the source, every row must be compared. Only new and changed rows are written.
  • The key is one or more columns of the result, named in KEY(...) without regard to case. An existing table must have the same columns, with the same names, in the same order as the result.
  • The values are compared after conversion to the table's column types: a DECIMAL(10,2) column rounds 3.004 to 3.00, so that value is unchanged; text is compared exactly, letter case and trailing spaces included. A change from or to NULL is an update.
  • Nothing is written, and the error says why, when a key column is not in the result, when a key value is NULL, when a key appears twice in the result or already twice in the table, when a value cannot be converted to the column's type, or when the columns differ.
  • The changes are made in one transaction: after a failure or a CTRL+C, the table is unchanged. A table that does not exist yet is created with a unique index on the key; if the MERGE then fails, the empty table is removed.
  • The rows are first copied into a temporary table of the destination database: its file grows by about the size of the result, and H2 reuses that space afterwards. A table with an index on the key columns is merged faster.

Large exports

  • An XLSX export to a new file, a new worksheet or with MODE OVERWRITE streams its rows: the memory it needs does not grow with the number of rows. The rows wait in compressed temporary files in a folder of the system temporary folder, deleted at the end of the export. A worksheet holds at most 1048575 rows.
  • MODE APPEND to an existing XLSX worksheet, and the other worksheets of an existing file, are held in memory. ODS files are built in memory and are much slower than XLSX and CSV for large results.
  • An export that runs longer than 10 seconds prints its progress every 10 seconds, for example DUMP in progress: 120000 row(s) so far (0:00:20). OUTPUT QUIET hides these lines in a Script.
  • From PostgreSQL, the rows are read in batches when Autocommit is off. With auto-commit on, the PostgreSQL driver reads the whole result into memory first.

Completion, cancellation and failures

  • After the success message, a second line gives the date and time the export finished, in local time with its UTC offset, and how long it took: Completed: 2026-10-14 19:25:43 +02:00 (0:01:12).
  • CTRL+C cancels the export: the source query is cancelled where the database driver supports it, and the writing stops between two rows. A message says what was left behind. A file is written to a temporary file and replaces the destination only at the end, so it is never left half written and an existing file stays as it was. An H2 destination receives no row; with MODE OVERWRITE the table may already have been recreated empty, because H2 commits DROP TABLE and CREATE TABLE at once. On PostgreSQL with Autocommit off, a cancelled query aborts the current transaction: run ROLLBACK before the next statement.
  • A file that another program holds open (Excel, on Windows) cannot be replaced: the export fails and the file is unchanged. When BroadSQL runs out of memory, the export fails with a message that says what was left behind, as after CTRL+C.

A table name alone

DUMP <table> alone, with no other word, keeps its original file naming and format choice: the file is named after the table (the worksheet too) and its format is DefaultFileFormat, or tab separated text beyond MaxRowXLSX rows. It is written by the same writers as every other form, so a CSV file uses the CsvSeparator setting.

Time zones

Time-zone-aware timestamps are exported the same way on every computer: the text formats keep each value's own offset (2024-01-15 10:00:00+05:00), XLSX and ODS hold a native date and time cell of the same moment in UTC, and H2 keeps the offset. Dates, times and timestamps without a time zone are never converted.

Protected destinations

The Connections Definition File ($CDF) is a protected BroadSQL system database: AS H2 refuses it as a destination, and so does any other connection that opens the same database file. Nothing is written.

Used in these how-tos

Last modified in release 5.5.0.

← All commands

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