PULL

PULL copies a query's current results into one of three families of destination, all under one unified syntax:

• a table of a local H2 database (AS H2);
• a tab of an Excel or OpenDocument spreadsheet file (AS XLSX / AS ODS);
• a whole flat file (CSV, plain text, JSON, Markdown, or HTML) (AS CSV / AS TXT / AS JSON / AS MD / AS HTML).

None of the three need to exist beforehand: a table's columns and types, or a spreadsheet's headers, are derived from the query itself.

Every example on this page uses the sample WORLD database's COUNTRY and CITY tables, which ship with BroadSQL for exactly this purpose.

COUNTRY (ACTIVITY_STATUS_ID, CAPITAL, CODE, CODE2, CONTINENT, CURRENCY_CODE, FIPS, GEONAMEID, GNP,
         GNPOLD, GOVERNMENTFORM, HEADOFSTATE, INDEPYEAR, INSERTION_DATE, ISO_NUMERIC, LAST_UPDATE,
         LIFEEXPECTANCY, LOCALNAME, NAME, NEIGHBOURS, PHONE, POPULATION, REGION, SURFACEAREA, TLD)

CITY (ACTIVITY_STATUS_ID, COUNTRYCODE, DISTRICT, ID, INSERTION_DATE, LAST_UPDATE, NAME, POPULATION)

Recommended alternative to EXPORT/DUMP for a one-shot export: PULL lets you name the destination format and file explicitly, in one command, instead of EXPORT's mode-toggle workflow or DUMP's automatic row-count-based format choice; and it's the only way to get JSON, Markdown, or HTML output at all, since EXPORT/DUMP don't have those. EXPORT/DUMP still work exactly as before and aren't going anywhere (see Export & Dump), but for a new script, PULL is the more explicit, more predictable choice. Two differences worth knowing before you switch: PULL's CSV/TXT output doesn't honor SET SEP (see "Writing to a text file" below), and PULL rejects a column it can't map (large objects, binary, structural types) with a clear error rather than writing whatever the driver happens to return for it; true for every format, including the spreadsheet and H2 destinations. Oracle's TIMESTAMP WITH TIME ZONE and TIMESTAMP WITH LOCAL TIME ZONE columns are supported despite not being one of the standard JDBC date/time types: every format writes them as a plain timestamp, without the zone or offset.

Syntax

PULL <source> TO <name>.<table> AS H2 [MODE OVERWRITE | MODE APPEND KEY(<column>)] ;
PULL <source> TO <name>.<tab>   AS XLSX | ODS ;
PULL <source> TO <name>         AS CSV | TXT | JSON | MD | HTML ;

<source> ::= <table or view name>
           | /
           | ( <query> )

• <source>: what to copy.
  • A bare table/view name: shortcut for SELECT * FROM <name>.
  • /: the last query you ran.
  • A parenthesized query: parentheses are always mandatory here.
• The destination's shape depends on the format.
  • AS H2/AS XLSX/AS ODS take <name>.<table-or-tab>: a dot is required.
  • AS CSV/AS TXT/AS JSON/AS MD/AS HTML take a bare <name>: a dot is a syntax error, since a flat file has no tab/table part to address after one.
• MODE is only meaningful for AS H2 (see "MODE OVERWRITE"/"MODE APPEND KEY" below). Every other format doesn't accept a MODE clause at all: each always fully (re)writes its destination (the named tab for XLSX/ODS, the whole file for every flat-file format).

Writing to a local H2 database (AS H2)

<name> (before the dot): the target H2 database. If it already names a connection, that connection is reused (it must be H2: PULL refuses to touch a connection of any other type). Otherwise a new H2 database is created automatically and registered under that name. <table> (after the dot): the table to write into that database.

Source form

Destination

MODE OVERWRITE

MODE APPEND KEY(<column>)

How MODE APPEND KEY(<column>) behaves

• First run (the target table doesn't exist yet): behaves like OVERWRITE's table creation, minus the drop: every row the query returns is loaded.
• Later runs: BroadSQL reads the current maximum value of <column> already in the target table, then only fetches and inserts source rows whose <column> is strictly greater. No row already in the target is ever updated or deleted.
• Re-running with no new source rows inserts zero rows: safe to run again at any time.
• <column> must be a single column of a numeric or date/time type (composite keys are not supported by APPEND).
• The source query must be a simple, single-table query: see "Not supported" below for the exact boundary.

Typical use: mirror a table that gets old rows purged at the source (e.g. every 3 months) into a local H2 database with a much longer retention window, kept current by re-running the same command, with no manual bookkeeping:

PULL CITY TO ARCHIVE.CITY AS H2 MODE APPEND KEY(ID);

Run again next week, next month, whenever; only the rows added at the source since the last run are added locally; everything already pulled stays exactly as first captured.

Writing to a spreadsheet (AS XLSX / AS ODS)

<name> (before the dot): a plain file name, resolved to <default export folder>/<name>.xlsx or .ods. Unlike AS H2, there is no connection lookup: it's just a file. <tab> (after the dot): the tab to write. Writing a tab that already exists in the file erases and replaces just that tab: every other tab is left completely untouched, whether the file was built by an earlier PULL or by something else entirely (a hand-built spreadsheet, or one produced by EXPORT/DUMP). This is the main advantage over EXPORT/DUMP: several PULL calls, each naming a different tab, build up one multi-tab file over time.

Every file also gets a QUERIES tab, maintained automatically: one row per data tab, recording that tab's query, the date it was last pulled, the source connection, and how many rows it holds. Re-pulling a tab updates its row in place rather than adding a new one. QUERIES is a reserved tab name: you can't PULL a tab called QUERIES yourself.

Writing to a flat file (AS CSV / AS TXT / AS JSON / AS MD / AS HTML)

<name>: a plain file name with no dot at all (a flat file has no tab/table part to address after one), resolved to <default export folder>/<name>.<extension>. Always a full, clean overwrite of the whole file: there's no equivalent of AS XLSX/AS ODS's "just this tab" for a single flat file, and no per-file metadata (each file stays plain and self-contained: nothing is prepended to it).

CSV / TXT

The CSV field separator is not SET SEP. AS CSV uses a dedicated setting, CsvSeparator in BroadSQL.ini (a single character, e.g. ; or ,, defaults to ; if the setting is absent). AS TXT always uses a tab, regardless of any setting. Neither is affected by SET SEP, and there is no option to pick a different separator (e.g. a pipe) for a PULL-generated CSV/TXT file: a universal, well-formed file is preferred over a customizable one here. If you need a specific, non-standard separator, use EXPORT/DUMP with SET SEP instead (see Export & Dump).

Both formats quote a field that contains the separator, a double quote, or a line break (doubling any embedded quote), so a value like O'Brien, Jr. never corrupts the file even with a comma separator.

JSON

A single JSON array, one object per row, keyed by column name: the shape most tools expect when asked for "the data as JSON". A number is written as a real JSON number when it's exact; a DECIMAL too precise to survive as a JSON number (which most parsers treat as a double) falls back to a quoted string holding its exact value, the same rule already used for Excel. Dates/times are ISO-8601 (2024-01-15, 14:30:00, 2024-01-15T14:30:00), the convention most JSON-consuming tools expect. A NULL is a literal, unquoted null.

Markdown

A GitHub-Flavored-Markdown table (header row, separator row, data rows): pastes cleanly into GitHub/ GitLab issues and PRs, Confluence, Notion, and Slack. A literal | in a value is escaped as \|; an embedded line break becomes <br> (rendered as a line break by every major GFM tool, since a raw newline would otherwise split one row into two broken ones). A NULL is a blank cell.

HTML

A bare <table>...</table> fragment: deliberately not a full, standalone web page (no <html>/ <head>/<body>), so it pastes cleanly into an existing email or wiki page rather than fighting with a wrapper document. The header row is lightly styled inline (bold, light background) so the styling survives the paste. &, <, > are escaped; a NULL is an empty cell.

Not supported

Related pages

• Command reference: arguments, generated from the command's own code, always up to date.
• Export & Dump: the original way to write query results and whole tables to a file; still fully supported.