BroadSQL Download
On this page
Documentation / Commands / PULL

PULL

Core command

Compatibility alias of DUMP: exports a query's results to a file or an H2 database.

Syntax

PULL <source> [TO <destination>] [AS <format>] [MODE <mode>] [OPEN]

Quick start

PULL CUSTOMER TO WORKCOPY.CUSTOMER AS H2;

Copies every row of CUSTOMER into the table CUSTOMER of the local H2 database WORKCOPY, created if needed.

Arguments

ArgumentRequiredValuesDefault
<source>YesA table name, / (the previous result), (<query>), LIB <script>, @<script> or API RESULT.None
TO <destination>No. Required for AS H2.A file name. <file>.<worksheet> for XLSX and ODS. <connection>.<table> for H2.Named after the table or the Script; results otherwise
AS <format>NoCSV, TEXT, JSON, XLSX, ODS, H2, MD, HTMLThe DefaultFileFormat setting
MODE <mode>No. XLSX, ODS and H2 only.OVERWRITE, APPEND. H2 also: APPEND KEY(<column>) [FORCE] and MERGE KEY(<column>[, <column> ...])OVERWRITE
OPENNo. XLSX and ODS only.Opens the file after the exportNot opened

Examples

PULL CUSTOMER TO WORKCOPY.CUSTOMER AS H2;
PULL CUSTOMER TO WORKCOPY.CUSTOMER AS H2 MODE APPEND KEY(ID);
PULL (SELECT * FROM NEW_ORDERS) TO WORKCOPY.ORDERS AS H2 MODE APPEND;
PULL (SELECT ID, NAME FROM CUSTOMER WHERE COUNTRY = 'FR') TO REPORT.FRENCH_CUSTOMERS AS XLSX OPEN;
PULL CUSTOMER TO REPORT_CUSTOMERS AS CSV;
PULL API RESULT TO API_USERS AS JSON;
PULL (SELECT * FROM ORDERS WHERE CUSTOMER_ID = ${id}) TO WORKCOPY.ORDERS AS H2 MODE APPEND KEY(ID);
PULL CUSTOMER TO WORKCOPY.CUSTOMER AS H2 MODE MERGE KEY(ID);

Details

The same command as DUMP

PULL runs exactly like DUMP: the same sources, destinations, formats, write modes, defaults and files, and the same completion, cancellation and failure messages. Only its success message reads pulled into instead of exported to. Every existing PULL command line keeps working; new scripts use DUMP.

H2 destinations

  • MODE OVERWRITE, the default: the table is dropped and recreated from the result at every run.
  • MODE APPEND KEY(<column>) [FORCE]: 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 APPEND without KEY, and MODE MERGE KEY(<column>[, <column> ...]): as for DUMP.
  • 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.
  • A destination 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.

Last modified in release 5.5.0.

← All commands

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