On this page

DUMP

Core command PULL

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

Argument	Required	Values	Default
`<source>`	Yes	A table name, `/` (the previous result), `(<query>)`, `LIB <script>`, `@<script>` or `API RESULT`.	None
`TO <destination>`	No. Required for `AS H2`.	A file name, optionally with a folder; `<file>.<worksheet>` for XLSX and ODS; `<connection>.<table>` for H2. Between double or single quotes, worksheet included, when it contains spaces. The extension comes from the format.	Named after the table or the Script; `results` otherwise
`AS <format>`	No	`CSV`, `TEXT`, `JSON`, `XLSX`, `ODS`, `H2`, `MD`, `HTML`	The `DefaultFileFormat` setting
`MODE <mode>`	No. XLSX, ODS and H2 only.	`OVERWRITE`, `APPEND`. H2 also: `APPEND KEY(<column>) [FORCE]` and `MERGE KEY(<column>[, <column> ...])`	`OVERWRITE`
`OPEN`	No. XLSX and ODS only.	Opens the file after the export	Not opened

Examples

The previous result, as a text file

SELECT * FROM CUSTOMER WHERE COUNTRY = 'FR';
DUMP / TO customers AS TEXT;

A table into the worksheet country of export.xlsx

DUMP COUNTRY TO export.country AS XLSX;

The same, in a file name with spaces, as XLSX and as ODS

DUMP COUNTRY TO "my export file.country" AS XLSX;
DUMP COUNTRY TO 'my export file.country' AS ODS;

A file in another folder

DUMP COUNTRY TO "C:\temp\my export file.country" AS XLSX;

A Scripts Library Script, with an argument

DUMP LIB monthly.bsql region='EU' TO revenue_eu AS CSV;

A file named by a variable

LET day = SELECT CURRENT_DATE;
DUMP CUSTOMER TO customers_${day} 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);

PULL is the same command

PULL CUSTOMER TO customers AS CSV;

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

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 |

Details

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

PULL

PULL is accepted as another keyword of DUMP: PULL CUSTOMER TO customers AS CSV; is the command DUMP CUSTOMER TO customers AS CSV;. Both words run the same command, with the same rules, files and messages, which always name DUMP. Scripts written with PULL keep working.

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.
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: an H2 database in the export folder, registered as a standalone connection of the built-in LOCAL Environment. Its name has at most 15 characters and no character that a file name cannot hold. Every successful run adds a row to the table BROADSQL_PULL_AUDIT of the destination database: the table, the query, the time, the source connection, the number of rows and the mode.
MD and HTML: a table.

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

The destination

The destination after TO is a file name, optionally with a folder, then, for XLSX and ODS, a dot and the worksheet; for H2, a connection name, a dot and the table:

DUMP COUNTRY TO export.country AS XLSX; writes the worksheet country of the file export.xlsx. The part after the dot is always the worksheet, never the file's extension: the extension comes from the format (.xlsx for XLSX, .ods for ODS, .csv for CSV...).
Without a dot, the worksheet is DATA. CSV, TEXT, JSON, MD and HTML files have no worksheet: their destination has no dot.
A destination that contains spaces is written between quotes, double or single, around the whole destination, worksheet included: TO "my export file.country" AS XLSX and TO 'my export file.country' AS XLSX both write the worksheet country of my export file.xlsx; AS ODS writes my export file.ods. TO "my export file".country is refused: the quotes surround the complete destination. Inside quotes, the quote character is written twice to stand for itself: TO 'O''Brien report' AS CSV.
The destination may start with a folder: TO "C:\temp\my export file.country" AS XLSX writes C:\temp\my export file.xlsx. A relative folder is in the export folder: TO monthly\report.country writes monthly\report.xlsx in the export folder. The folder must exist; a dot in a folder name never starts the worksheet. An H2 destination is a connection name, never a path: its database is created in the export folder.
A missing closing quote, a quote inside an unquoted destination, an empty name and a character that a file name cannot hold are errors, and nothing is written. The same rules apply at the prompt, in a Script and in the BroadSQL Editor.

${name} scripting variables are replaced in the whole statement before DUMP reads it, the destination included: LET day = SELECT CURRENT_DATE; then DUMP CUSTOMER TO customers_${day} AS CSV; writes customers_2026-10-19.csv. See Variables.

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): the table is created at the first run; every later run inserts only the rows whose key is greater than the table's current maximum, read from the table at each run. The key is a numeric, date or time column of the result. The query may use a JOIN, but not a comma separated or derived FROM, nor any aggregation. A key that the database reports as possibly NULL is refused; FORCE accepts a key whose nullability the driver cannot report. An outer join can produce NULL keys that some drivers do not report: such rows would be skipped by every later run.
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.

LOAD, the import command
Export guide
Variables, ${name} in any statement
Exporting a Script's result

Used in these how-tos

Last modified in release 5.5.5.

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