LOAD

Syntax

LOAD FROM filename [ DELIMITER delimiter]
{
  INSERT INTO table-specification [ ( column [,...]) ]
|
  insert-string
}

where table-specification is:

[dbname[@dbserver]:][owner.]table

1. filename is a string expression containing the name of the file the data is read from.
2. delimiter is the character used as the value delimiter. When not specified, default is | pipe, or DBDELIMITER environment variable when set. The DELIMITER clause can also specify "CSV" or "TSV", to get respectively a Comma Separated Values format, or a TAB Separated Values format.
3. The INSERT clause is a pseudo INSERT statement (without the VALUES clause), where you can specify the list of columns in parentheses.
4. dbname identifies the database name.
5. dbserver identifies the database server (INFORMIXSERVER).
6. owner identifies the owner of the table, with optional double quotes.
7. table is the name of the database table.
8. column is a name of a table column.
9. insert-string is a string expression containing the pseudo-INSERT statement.

Usage

The LOAD instruction reads serialized data from an input file and inserts new rows in a database table specified in the INSERT clause. A file created by the UNLOAD statement can be used as input for the LOAD statement if its values are compatible with the schema of table.

102|Mike|12/24/2020|
192|Tom|04/29/2019|

The LOAD statement must include a pseudo-INSERT statement (either directly or as text in a variable) to specify where to store the data. LOAD appends the new rows to the specified table, synonym, or view, but does not overwrite existing data. It cannot add a row that has the same key as an existing row.

Avoid use of the the dbname, dbserver and owner prefix of the table name for maximum SQL portability.

The number and the order of columns in the INSERT statement must match the values of the input file.

The LOAD instruction cannot be prepared with a PREPARE statement. However, LOAD can take a string literal as parameter, that allows to build the INSERT statement at runtime.

The variable or string following the LOAD FROM keywords must specify the name of a file of ASCII characters (or characters that are valid for the current locale) that holds the data values that are to be inserted.

Each set of data values in filename that represents a new row is called an input record. Each input record must contain the same number of delimited data values. If the INSERT clause has no list of columns, the sequence of values in each input record must match the columns of table in number and order. Each value must have the literal format of the column data type, or of a compatible data type.

If LOAD is executed within a transaction block (BEGIN WORK / COMMIT WORK), the rows inserted by the LOAD instruction are part of the transaction. With some database servers the insert rows remain locked until the COMMIT WORK or ROLLBACK WORK statement terminates the transaction. Consider locking the whole table to reduce the lock resources allocated by the database server.

If the database does not support transactions, a failing LOAD statement cannot remove any rows that were loaded before the failure occurred. You must manually remove the already loaded records from either the load file or from the receiving table, repair the erroneous records, and rerun LOAD.

If the database supports transactions, you can do the following actions:

• Run LOAD as a singleton transaction, so that any error causes the entire LOAD statement to be automatically rolled back.
• Run LOAD within an explicit BEGIN WORK / COMMIT WORK transaction block, so that a data error merely stops the LOAD statement in place with the transaction still open.

When the LOAD instruction is not surrounded by BEGIN WORK and COMMIT WORK or ROLLBACK WORK instructions, terminating the transaction when LOAD is finished will automatically close cursors not defined WITH HOLD option. To keep cursors open, either use the WITH HOLD option in DECLARE CURSOR, or surround the DECLARE, OPEN and LOAD instruction with an explicit BEGIN WORK / COMMIT WORK.

A single character delimiter instructs LOAD to read data in the default format. When using "CSV" or "TSV" as delimiter specification, the LOAD instruction will read the data in CSV or TSV format.

If the DELIMITER clause is not specified, the delimiter is defined by the DBDELIMITER environment variable. If the DBDELIMITER environment variable is not set, the default is a | pipe. The field delimiter can be a blank character. It cannot be backslash or any hexadecimal digit (0-9, A-F, a-f). If the delimiter specified in the DELIMITER clause is NULL, the runtime system will use the default delimiter or DBDELIMITER if the variable is defined.

At this time, data type description of the input file fields is implicit; in order to create the SQL parameter buffers to hold the field values for inserts, the LOAD instruction uses the current database connection to get the column data types of the target table. Those data types depend on the type of database server. For example, IBM® Informix® DATE columns do not store the same data as the Oracle® DATE data type. Therefore, be careful when using the LOAD/UNLOAD instructions; if the application connects to different kinds of database servers, it can result data conversion errors.

Pay attention to numeric (DECIMAL, MONEY) and date/time values (DATE, DATETIME): These must match the current format settings (DBFORMAT, DBDATE). As a general programming pattern, use simple INSERT statements to load default and configuration data into your database, in order to be independent from the numeric and date format settings.

When the target column is a SERIAL or BIGSERIAL column, values in the input file can be represented as 0 to tell the database server to supply a new serial value. If the values is different from zero, it will be inserted as is, if the database engine and driver support explicit serial value insertion.

Rules for single-char delimiter format

Single-char delimiter formatting rules apply when a unique character is specified as delimiter. These rules are slightly different as when using a DSV delimiter.

Character strings must be encoded in the current application locale: Not charset conversion is done.

Character-type data need a backslash ( \ ) before any literal backslash or delimiter character and before a NEWLINE character in a character value. When reading character-type data, values can have more characters than the declared maximum length of the column, but any extra characters are ignored. Blank values can be represented as one or more blank characters between delimiters, but leading blanks must not precede other CHAR, VARCHAR, or TEXT values.

Numeric-typed data representation depends on DBFORMAT/DBMONEY environment variables.

DATE value formatting is based on the DBDATE environment variable. The day and month must be a 2-digit number, and the year must be a 4-digit number.

When reading for MONEY types, values can include currency symbols, but these are not required.

DATETIME values are represented in the format year-month-day hour:minute:second.fraction or a contiguous subset. Time units outside the reference type precision are omitted. The year must be a four-digit number; all other time units (except fraction) require two digits.

INTERVAL values are formatted year-month or day hour:minute:second.fraction or a contiguous subset. Time units outside the reference type precision are omitted.

BYTE values must be ASCII-hexadecimals; without leading or trailing blanks.

NULL values of any data type are represented by consecutive delimiters, without any characters between the delimiter symbols.

The backslash symbol (\) serves as an escape character to indicate that the next character in a data value is a literal that needs to be escaped, such as a backslash, NEWLINE, or the delimiter character.

Rules for Delimiter Separated Values format

The Delimiter Separated Values (DSV) formatting rules apply when using "CSV" (Comma Separated Values), "TSV" (TAB Separated Values), or "DSV=sep" as delimiter.

DSV serialization and de-serialization rules are similar to the single-char delimiter formatting rules when using a regular single-character delimiter, with the following differences:

• Leading and trailing blanks are kept (no truncation).
• No ending delimiter is expected at the end of the input record.
• Values might be surrounded with " double quotes, if the data contains characters such as the " double quote, \ backslash, new-line, or the delimiter character, and that character is not escaped.