On this page

LOAD

Core command LO

Loads the rows of a CSV file into a table, with safe INSERT statements, all or nothing.

Syntax

LOAD [<schema>.]<table> <file> [PREVIEW | EXECUTE]

Quick start

LOAD CUSTOMER customer.csv PREVIEW;

Checks customer.csv against CUSTOMER without writing.

LOAD CUSTOMER customer.csv;

Checks again, then asks before inserting the rows.

Arguments

Argument	Required	Values	Default
`<table>`	Yes	The table to insert into	None
`<schema>.`	No	The schema of the table, followed by a dot	The current schema
`<file>`	Yes	The CSV file; a name without a folder is in the export folder	None
`PREVIEW` or `EXECUTE`	No. `EXECUTE` is required to write inside a Script.	`PREVIEW` only checks; `EXECUTE` writes without asking	Check, then ask before writing

Examples

LOAD CUSTOMER customer.csv
LOAD CUSTOMER customer.csv PREVIEW
LOAD CUSTOMER customer.csv EXECUTE
LOAD CRM.CUSTOMER c:\temp\customer.csv EXECUTE

Example session

WORLD> LOAD MY_VISIT my_visits.csv PREVIEW;
LOAD PREVIEW
------------------------------------------------------------
Target      : WORLD / PUBLIC.MY_VISIT
Mode        : INSERT
Source      : c:\temp\my_visits.csv
Input rows  : 3
Valid rows  : 3
Rejected    : 0

Columns:
CITY_ID -> CITY_ID  INTEGER
VISIT_DATE -> VISIT_DATE  DATE
NOTE -> NOTE  VARCHAR

No changes have been made.

Illustrative output: the values come from the WORLD sample and the practice connections of the how-to on QA and production copies of WORLD, and the layout from this release; it was not captured from a BroadSQL run, and your timings will differ.

Details

Safe inserts

Every value is bound as a JDBC parameter, never written into the SQL text, and the table and its columns are checked against the database's metadata before any SQL is built. The file is a CSV file separated by the CsvSeparator setting (a semicolon by default). A file name without a folder is read from the export folder (DefaultFolder).

Preview, confirmation and EXECUTE

PREVIEW checks the file against the table (column mapping, conversion of every value) and reports the outcome, without writing anything or asking anything.
Without a mode, LOAD runs the same checks, then, when they pass, asks Load <n> rows into <table>? [y/N]: only y or yes writes; the default is No. Inside a Script (@, LIB RUN), where nobody can answer, it refuses instead and writes nothing.
EXECUTE writes at once, without asking, at the prompt or in a Script.

All or nothing

Any failed check (a column of the file that the table does not have, or a value that cannot be converted) refuses the whole load: valid rows are never loaded alone. The rows are sent in batches, but nothing is committed until every batch has succeeded, and any failure rolls back everything this command wrote.

Older form

LOAD CREATE <table> <file> still works: CREATE is the only mode, so the word is optional. LOAD UPDATE is refused with a message explaining what to do instead.

Import guide, the CSV format and conversion rules
DUMP, the export command
Settings, CsvSeparator and DefaultFolder

Used in these how-tos

Last modified in release 5.5.0.

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