Exporting data

Export a table, a query result or a Script's result to a file or to a local H2 table with DUMP. The Import guide covers loading CSV data into an existing table.

Before you start

• A connection to the database that holds the data (CONNECT <id>;).
• The export folder, DefaultFolder in BroadSQL.ini (see Application settings): every file is written there unless you give a path.

The examples use the WORLD sample database, which ships with BroadSQL, and its COUNTRY and CITY tables.

DUMP command and basic workflow

Step 1. Run a query and look at the result:

SELECT CODE, TRIM(NAME) AS NAME FROM COUNTRY WHERE REGION = 'Baltic Countries' ORDER BY CODE;

Step 2. Export exactly that result, without running the query again, with /:

DUMP / TO baltic AS CSV;

Step 3. BroadSQL reports where the file is and when the export finished:

WORLD> DUMP / TO baltic AS CSV;
3 row(s) exported to c:\temp\baltic.csv.
Completed: 2026-10-18 10:02:11 +02:00 (0:00:00).

The file holds the header and the rows (CsvSeparator decides between ; and ,). TRIM removes the spaces that pad WORLD's fixed-length NAME column:

CODE;NAME
EST;Estonia
LTU;Lithuania
LVA;Latvia

The Completed: line gives the date and time the export finished (local time, with its UTC offset) and how long it took. It is printed only after the destination is complete: never after a failure, a refusal or Ctrl+C.

The general form is DUMP <source> [TO <destination>] [AS <format>]; the exact syntax, every argument and its default are on the DUMP page.

Exporting data with DUMP

DUMP writes tabular data to one of three families of destination, all under one syntax:

• a whole flat file: CSV, TEXT, JSON, Markdown or HTML (AS CSV / AS TEXT / AS JSON / AS MD / AS HTML), see Files and spreadsheets;
• a worksheet of an Excel or OpenDocument spreadsheet file (AS XLSX / AS ODS), see Files and spreadsheets;
• a table of a local H2 database (AS H2), see Local H2 datasets.

None of them need to exist beforehand: a table's columns and types, or a spreadsheet's headers, are derived from the data itself. DUMP exports data; it is not a database backup tool. A column that cannot be mapped (large objects, binary, structural types) is rejected with a clear error before anything is written, for every format.

The source is one of:

• a table or view name: every row of that table. A name containing spaces or other special characters is written in double quotes (DUMP "Country Notes" TO notes AS CSV, for a table you created under that name). BroadSQL finds the table the same way as DESCR (the name as typed, then in upper case, then in lower case) and reads exactly that table, so DUMP "Country" never exports a different table named COUNTRY;
• /: the previous tabular result (see The previous result);
• a query in parentheses, run now. The query may use variables written ${name} (see Script variables), passed to the database as typed values;
• LIB <script> or @<script>: the final result of a Scripts Library Script (see Exporting a script's result);
• API RESULT: the last RUN result (see Exporting an API execution result).

Destination and format, in short:

• Without TO, the file is named after the table, after the Script (DUMP LIB sales.sql writes sales.<ext>), or after the DefaultExtFileName setting (results) for / and a query.
• AS XLSX, AS ODS and AS H2 take <name>[.<worksheet-or-table>], DATA when omitted: DUMP COUNTRY TO REPORT AS XLSX; is the same as DUMP COUNTRY TO REPORT.DATA AS XLSX;. The flat formats take a bare <name>: a dot is a syntax error. AS H2 always needs an explicit TO <connection>.<table>.
• Without AS, BroadSQL uses DefaultFileFormat from BroadSQL.ini; an explicit AS <format> always wins. If DefaultFileFormat is absent, ODS is used; if it is not one of XLSX, ODS, CSV, TXT, DUMP refuses the form without AS with a configuration error.
• MODE applies to AS H2, AS XLSX and AS ODS (see MODE OVERWRITE, MODE APPEND, MODE APPEND KEY and MODE MERGE KEY). The flat formats always rewrite the whole file.
• The Connections Definition File ($CDF) is never a destination (see Local H2 datasets).

The previous result (DUMP /)

DUMP / exports the most recent tabular result of the session, exactly as it was displayed: the query is not run again, so rows added or changed since then do not appear in the file.

SELECT ID, NAME, POPULATION FROM CITY WHERE COUNTRYCODE = 'FRA' ORDER BY POPULATION DESC;
DUMP / TO french_cities AS CSV;
DUMP / TO french_cities.TOP AS XLSX;

• Commands that do not produce a table (HELP, SET ..., an UPDATE) leave the previous result in place, so DUMP / still exports it.
• With no previous result in the session, DUMP / fails with "No previous tabular result is available to dump" and writes nothing.
• Only a complete result is exported. The limit is the one you configured: if the display stopped at MaxRowsOnScreen, DUMP / refuses (it would otherwise write a partial file) and suggests running the query through DUMP (<query>) instead, which exports every row. A result displayed in full is exported in full, whatever its size.
• A value the database can display but not return with its type (for example NaN or Infinity in a numeric column) never prevents the SELECT from being displayed. DUMP / then refuses to export that result, naming the column and the value, and writes nothing: convert the column to text in the query (for example CAST(<column> AS VARCHAR)), run it again, then DUMP /.

Practical examples

Export a query to CSV for a colleague, running it now:

DUMP (SELECT ID, TRIM(NAME) AS NAME, POPULATION FROM CITY WHERE COUNTRYCODE = 'FRA') TO french_cities AS CSV;

Export what you just looked at, in the format set by DefaultFileFormat:

SELECT CODE, NAME FROM COUNTRY WHERE INDEPYEAR IS NULL ORDER BY CODE;
DUMP / TO no_independence_year;

Keep a local H2 copy up to date by key, then export it to Excel:

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

The format of each destination is described in Files and spreadsheets and Local H2 datasets.

Not supported

Large exports, progress and Ctrl+C

A long export prints its progress every 10 seconds (DUMP in progress: 120000 row(s) so far (0:00:20).), and Ctrl+C stops it at once: a file is replaced only at the very end, so a cancelled or failed export leaves an existing file unchanged and no half-written file. For a large result, prefer CSV or a new XLSX file, whose memory use does not grow with the number of rows; ODS and MODE APPEND to an existing XLSX worksheet are held in memory.

The exact behavior (memory per format, temporary files, what Ctrl+C leaves behind for each destination) is on the DUMP page, under Large exports and Completion, cancellation and failures; the spreadsheet limits are in Large spreadsheets. See also Ctrl+C during a command.

Reference details

Temporary files

While DUMP LIB and DUMP @ run a Script, they collect its result in a temporary database in the system temporary folder (%TEMP% on Windows, usually /tmp on Linux): files named broadsql_dump_<32 letters and digits>.mv.db. They are deleted as soon as the export ends, whether it succeeded or not.

If BroadSQL is stopped while such an export runs (a crash, the window closed, the computer turned off), the files stay. At its next start, BroadSQL deletes them, but only when:

• every file of that export was last modified more than 24 hours ago, and
• no program has the database open: BroadSQL takes the lock the database engine holds on an open database, and keeps the files when it cannot.

Files that are recent or in use, files with another name, and folders are never touched. Nothing is shown on screen; the application log records how many were deleted.

Fractional seconds

Time and timestamp values keep their fractional seconds. The text formats (CSV, TEXT, JSON, MD, HTML) write yyyy-MM-dd HH:mm:ss (JSON: yyyy-MM-ddTHH:mm:ss) and HH:mm:ss, followed by the fraction the value carries, in groups of three digits: 2015-05-07 09:49:01 stays as it is, 2015-05-07 09:49:01.016 keeps .016, and a microsecond value keeps its six digits. No .000 is added to a whole second. XLSX stores date and time values to the millisecond (the finest precision of Excel) and displays the milliseconds of the values that have some; ODS keeps the value as it is.

Time-zone-aware timestamps

BroadSQL exports time-zone-aware timestamps (TIMESTAMP WITH TIME ZONE, PostgreSQL's timestamptz, Oracle's TIMESTAMP WITH TIME ZONE and TIMESTAMP WITH LOCAL TIME ZONE) deterministically: the same row gives the same file on every computer. BroadSQL never uses the computer's local time zone as an implicit conversion rule.

• The text formats keep the date, the time, the fractional seconds and the offset exactly as the database returns them. A value in UTC is written with +00:00.
• A spreadsheet cell has no time zone, so XLSX and ODS convert each value to UTC and store it as a real date/time cell, not as text. Sorting, filtering, formulas, formatting, pivot tables and date calculations work immediately in Excel and Calc, with no conversion of the column.
• DUMP / applies the same rules to the previous result.
• TIME WITH TIME ZONE values follow the same rules (UTC time in XLSX and ODS).
• A database driver that cannot return the offset gives BroadSQL only the moment itself: the text formats then write it in UTC (+00:00), never in the computer's time zone.

Time-zone-less DATE, TIME and TIMESTAMP values are exported as they are stored, with no time zone conversion in any format: 2024-01-15 10:00:00 stays 2024-01-15 10:00:00.

DUMP <table> alone

DUMP <table> with nothing else on the line keeps its original file naming and format choice:

• The file is written to DefaultFolder, named after the table exactly as typed (schema included: DUMP PUBLIC.COUNTRY produces PUBLIC.COUNTRY.<ext>); a spreadsheet's worksheet is named after the table too.
• At or below MaxRowXLSX rows (or when the row count cannot be determined), the format is DefaultFileFormat; above it, the file is always a tab separated .txt.
• The console reports "N records extracted to <file>", followed by the Completed: line.

The file itself is written like any other DUMP: a .csv uses CsvSeparator, and an .xlsx or .ods also gets the QUERIES worksheet. Add AS <format> or TO <destination> to use the options described above instead, for example DUMP PUBLIC.COUNTRY AS CSV;.

PULL, the compatibility alias

PULL runs exactly like DUMP: same sources, optional TO, destinations, formats, defaults and files, so every earlier PULL command keeps working. PULL / is the displayed result, like DUMP /: the query is never run again. Only its success message still says "pulled into". Use DUMP in new scripts. See PULL.

Related command reference

• Import / Export overview: choosing a destination, and the other pages of this section.
• DUMP: exact command arguments and examples.
• PULL: the compatibility alias.
• LOAD and Import: loading CSV data back into a table.
• Application settings: DefaultFolder, DefaultFileFormat, DefaultExtFileName, CsvSeparator, MaxRowXLSX.

Scripts from earlier releases

Earlier releases taught two commands that still run, unchanged, but are no longer documented:

MS Access files are the one exception: DUMP has no .mdb format. EXPORT <file>.mdb; still writes the legacy Access 2000 (.mdb) format, using the Jackcess library, for scripts that rely on it; this format is not developed further.

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