BroadSQL BroadSQL Downloads
Documentation / Commands / DUMP

DUMP

Core command

DUMP is the export command. DUMP <table>, DUMP / (the previous result, as displayed, without running the query again), DUMP (<query>) and DUMP LIB <script> or DUMP @<script> (the final result of a Scripts Library script) all accept [TO <destination>] [AS <format>]. Formats: CSV (comma or semicolon, from the CsvSeparator setting), TEXT (UTF-8, tab separated), JSON, XLSX and ODS (worksheet DATA unless named, e.g. TO report.MONTHLY), H2, MD and HTML. Without AS, the DefaultFileFormat setting applies; without TO, the file is named after the table, the script, or DefaultExtFileName (results). Files are written to the export folder (DefaultFolder). PULL is a compatibility alias of DUMP. DUMP / exports only a result displayed in full (never cut at MaxRowsOnScreen) and never runs the query again. DUMP <table> alone keeps its original file naming: format from DefaultFileFormat, or tab separated text beyond MaxRowXLSX rows. For AS H2, XLSX and ODS, MODE APPEND adds the rows to the existing table or worksheet when it has the same columns, with the same names, in the same order as the result (types are not compared, any query can be appended); a missing destination is created. The Connections Definition File ($CDF) cannot be a destination.

Arguments

<source> [TO <destination>] [AS <format>], where <source> is a table name, / (the previous result), a query in parentheses (which may use ${name} variables), LIB <script> or @<script> (a Scripts Library script, both forms are the same, followed by optional name=value arguments) or API RESULT, <destination> is a file name (optionally <name>.<worksheet> for XLSX/ODS, <connection>.<table> for H2), and <format> is CSV, TEXT, JSON, XLSX, ODS, H2, MD or HTML; AS H2, XLSX and ODS also accept [MODE OVERWRITE | MODE APPEND] (AS H2 also MODE APPEND KEY(<column>) [FORCE]), and AS XLSX and ODS a final [OPEN]

Examples

DUMP CUSTOMER;
DUMP CUSTOMER TO customers AS CSV;
SELECT * FROM CUSTOMER WHERE COUNTRY = 'FR';
DUMP /;
DUMP / TO customers AS TEXT;
DUMP (SELECT * FROM CUSTOMER WHERE COUNTRY = 'FR') TO customers.DATA AS XLSX;
DUMP LIB sales.sql;
DUMP LIB sales.sql TO sales AS JSON;
DUMP @sales.sql TO sales AS CSV;
DUMP LIB monthly.bsql region='EU' TO revenue_eu AS CSV;
LET c = 'FR';
DUMP (SELECT * FROM CUSTOMER WHERE COUNTRY = ${c}) TO fr AS CSV;
DUMP (SELECT o.ID, c.NAME AS CUSTOMER, o.AMOUNT FROM ORDERS o JOIN CUSTOMER c ON c.ID = o.CUSTOMER_ID) TO WORKCOPY.ORDERS AS H2 MODE APPEND;
DUMP / TO report.HISTORY AS XLSX MODE APPEND;

Notes

Exports tabular data to a file: DUMP <source> [TO <destination>] [AS <format>]. PULL is a compatibility alias of this command. DUMP writes data or results to a file; it is not a database backup.

Sources: a table or view name (DUMP CUSTOMER AS CSV; a name containing spaces or other special characters in double quotes, DUMP "Sales Data" AS CSV), read by the exact name of the table it designates, found as DESCR finds it; /, the previous tabular result exactly as it was displayed, without running the query again; a query in parentheses, run now, in which ${name} variables are passed to the database as typed values (`DUMP (SELECT * FROM orders WHERE customer_id = ${id}) TO orders AS CSV); LIB <script> [name=value ...]`, the final tabular result of a Scripts Library script, run as LIB RUN would run it, with the same named arguments, but without displaying its results (@<script>, as a Script is run at the prompt, is the same source: DUMP @sales.sql is DUMP LIB sales.sql); API RESULT, the last RUN result. Only the query in parentheses is read for variables: TO, AS and the other parts of the command are taken as written.

DUMP / exports only a complete result: if its display stopped at the configured MaxRowsOnScreen limit, it refuses and suggests DUMP (<query>); the query is never run again. DUMP LIB writes the file only when the script's final status is SUCCESS: nothing is written for COMPLETED_WITH_ERRORS, FAILED (a missing script or argument included) or CANCELLED, and an error states the status. Nothing is written either when the script produces no tabular result. A LET query of the script is never the exported result.

Formats: CSV (comma or semicolon, from the CsvSeparator setting, standard quoting), TEXT (UTF-8, always tab separated), JSON (an array of objects), XLSX and ODS (worksheet DATA unless named, as in TO report.MONTHLY), H2, MD and HTML. TXT is accepted as TEXT. Without AS, the DefaultFileFormat setting applies.

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/time cell of the same moment in UTC, and H2 keeps the offset. Time-zone-less dates, times and timestamps are never converted.

Files are written to the export folder (DefaultFolder). Without TO, the file is named after the table, after the library script, or after DefaultExtFileName (results) for / and a query. AS H2 always needs TO <connection>.<table>.

Write modes, for AS H2, AS XLSX and AS ODS: MODE OVERWRITE (the default) replaces the table or worksheet. MODE APPEND 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). Nothing else is compared: the data types are not, and the query is not examined, so a join, a subquery, a WITH query, a UNION or any other query the database runs 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. A result with no rows appends nothing and is not an error; NULL values are appended as NULL (an empty cell in a worksheet). MODE APPEND works with every source. AS H2 also accepts MODE APPEND KEY(<column>) [FORCE], described for PULL, which adds only the rows whose key is greater than the table's current maximum.

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.

DUMP <table> alone, with no other word, keeps its original file naming and format choice: the file is named after the table (worksheet named after the table 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.

See the Export guide for workflows and every format, and Exporting a Script's result in the SQL scripting guide.

Last modified in release 5.4.5.

← All commands