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.

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 on a schedule.
• <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, refreshed on a schedule 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.