LOAD TABLE Statement

Quick Links:

Syntax

[ INTO ] TABLE [ <owner>.]<table-name>
   ... ( load-specification [, …] )
   ...  { FROM | USING [ CLIENT ] FILE }  
   { '<filename>-<string>' | <filename>-<variable> } [, …]
   ... [ CHECK CONSTRAINTS { ON | OFF } ]
   ... [ DEFAULTS { ON | OFF } ]
   ... [ QUOTES OFF ]
   ... ESCAPES OFF
   ... [ FORMAT { ascii | binary | bcp } ]
   ... [ DELIMITED BY '<string>' ]
   ... [ STRIP { OFF | RTRIM } ]
   ... [ WITH CHECKPOINT { ON | OFF } ]
   ... [ BYTE ORDER { NATIVE | HIGH | LOW } ]
   ... [ LIMIT <number-of-rows> ]
   ... [ NOTIFY <number-of-rows> ]
   ... [ ON FILE ERROR { ROLLBACK | FINISH | CONTINUE } ]
   ... [ PREVIEW { ON | OFF } ]
   ... [ ROW DELIMITED BY '<delimiter-string>' ]
   ... [ SKIP <number-of-rows> ]
   ... [ HEADER SKIP <number> [ HEADER DELIMITED BY '<string>' ] ]
   ... [ WORD SKIP <number> ]
   ... [ ON PARTIAL INPUT ROW { ROLLBACK | CONTINUE } ]
   ... [ IGNORE CONSTRAINT constraint-type [, …] ]
   ... [ MESSAGE LOG ‘<string>’ ROW LOG ‘<string>’ [ ONLY LOG log-what [, …] ]
   ... [ LOG DELIMITED BY ‘<string>’ ]

load-specification - (back to Syntax) 
   { <column-name> [ column-spec ]
      | FILLER ( filler-type ) }

column-spec - (back to load-specification)
   { ASCII ( <input-width> )
   | BINARY [ WITH NULL BYTE ]
   | PREFIX { 1 | 2 | 4 }
   | <'delimiter-string'>
   | DATE ( <input-date-format> )
   | DATETIME ( <input-datetime-format> )
   | ENCRYPTED ( <data-type ‘key-string’> [, <‘algorithm-string’> ] )
   | DEFAULT <default-value> } 
   [ NULL ( { BLANKS | ZEROS | <'literal'>,  …} ) 

filler-type - (back to load-specification)
   { <input-width>
   | PREFIX { 1 | 2 | 4 }
   | <'delimiter-string'> 
   }

constraint-type - (back to Syntax)
   { CHECK <integer>
   | UNIQUE <integer>
   | NULL <integer>
   | <FOREIGN KEY><integer> 
   | DATA VALUE <integer>
   | ALL <integer>
   }

log-what - (back to Syntax)
   { CHECK
   | ALL
   | NULL
   | UNIQUE
   | DATA VALUE
   | FOREIGN KEY
   | WORD
   }

Parameters

(back to top)

FROM
identifies one or more files from which to load data. To specify more than one file, use a comma to separate each filename-string. The <filename-string> is passed to the server as a string. The string is therefore subject to the same formatting requirements as other SQL strings.
To indicate directory paths on Windows, the backslash character \ must be represented by two backslashes. Therefore, the statement to load data from the file c:\temp\input.dat into the Employees table is:
```
LOAD TABLE Employees
FROM 'c:\\temp\\input.dat' ...
```
The path name is relative to the database server, not to the client application. If you are running the statement on a database server on some other computer, the directory names refers to directories on the server machine, not on the client machine. When loading a multiplex database, use absolute (fully qualified) paths in all file names. Do not use relative path names.

Because of resource constraints, SAP IQ does not guarantee that all the data can be loaded. If resource allocation fails, the entire load transaction is rolled back. The files are read one at a time, and processed in the order specified in the FROM clause. Any SKIP or LIMIT value only applies in the beginning of the load, not for each file.

The LOAD TABLE FROM clause is deprecated, but may be used to specify a file that exists on the server. This example loads data from the file a.inp on a client computer.
```
LOAD TABLE t1(c1,c2,filler(30))
USING CLIENT FILE 'c:\\client-data\\a.inp'
QUOTES OFF ESCAPES OFF
IGNORE CONSTRAINT UNIQUE 0, NULL 0
MESSAGE LOG 'c:\\client-data\\m.log'
ROW LOG 'c:\\client-data\\r.log'
ONLY LOG UNIQUE
```

USING USING FILE loads one or more files from the server. This clause is synonymous with specifying the FROM <filename> clause. USING CLIENT FILE bulk loads one or more files from a client. The character set of the file on the client side must be the same as the server collation. SAP IQ serially processes files in the file list. Each file is locked in read mode as it is processed, then unlocked. Client-side bulk loading incurs no administrative overhead, such as extra disk space, memory or network-monitoring daemon requirements, but does forces single threaded processing for each file.
When bulk loading large objects, the USING CLIENT FILE clause applies to both primary and secondary files.

The LOAD TABLE statement can load compressed client and server files in the gzip format only. Any file with an extension ".gz" or ".gzip" is assumed to be a compressed file. Named pipes or secondary files are not supported during a compressed file load. Compressed files and uncompressed files can be specified in the same LOAD TABLE statement. Each compressed file in a load is processed by one thread.

During client-side loads, the IGNORE CONSTRAINT log files are created on the client host and any error while creating the log files causes the operation to roll back.

Client-side bulk loading is supported by Interactive SQL and ODBC/JDBC clients using the Command Sequence protocol. It is not supported by clients using the TDS protocol. For data security over a network, use Transport Layer Security. To control who can use client-side bulk loads, use the secure feature (-sf) server startup switch, enable the ALLOW_READ_CLIENT_FILE database option, and the READ CLIENT FILE access control.

CHECK CONSTRAINTS
evaluates check constraints, which you can ignore or log. CHECK CONSTRAINTS defaults to ON.
Setting CHECK CONSTRAINTS OFF causes SAP IQ to ignore all check constraint violations. This can be useful, for example, during database rebuilding. If a table has check constraints that call user-defined functions that are not yet created, the rebuild fails unless this option is set to OFF.
This option is mutually exclusive to the following options. If any of these options are specified in the same load, an error results:
- IGNORE CONSTRAINT ALL
- IGNORE CONSTRAINT CHECK
- LOG ALL
- LOG CHECK

DEFAULTS uses a column's default value. This option is ON by default. If the DEFAULTS option is OFF, any column not present in the column list is assigned NULL.
The setting for the DEFAULTS option applies to all column DEFAULT values, including AUTOINCREMENT.

QUOTES
indicates that input strings are enclosed in quote characters. QUOTES is an optional parameter and is ON by default. The quote character is either an apostrophe (single quote) or a quotation mark (double quote). The first such character encountered in a string is treated as the quote character for the string. String data must be terminated with a matching quote.
With QUOTES ON, column or row delimiter characters can be included in the column value. Leading and ending quote characters are assumed not to be part of the value and are excluded from the loaded data value.

To include a quote character in a value with QUOTES ON, use two quotes. For example, this line includes a value in the third column that is a single quote character:
```
‘123 High Street, Anytown’, ‘(715)398-2354’,’’’’
```
With STRIP turned on (the default), trailing blanks are stripped from values before they are inserted. Trailing blanks are stripped only for non-quoted strings. Quoted strings retain their trailing blanks. Leading blank or TAB characters are trimmed only when the setting is ON.

The data extraction facility provides options for handling quotes (TEMP_EXTRACT_QUOTES, TEMP_EXTRACT_QUOTES_ALL, and TEMP_EXTRACT_QUOTE). If you plan to extract data to be loaded into an IQ main store table and the string fields contain column or row delimiter under default ASCII extraction, use the TEMP_EXTRACT_BINARY option for the extract and the FORMAT binary and QUOTES OFF options for LOAD TABLE.
Limits:
- QUOTES ON applies only to column-delimited ASCII fields.
- With QUOTES ON, the first character of a column delimiter or row terminator cannot be a single or double quote mark.
- QUOTES ON forces single threaded processing for a given file.
- The QUOTES option does not apply to loading binary large object (BLOB) or character large object (CLOB) data from the secondary file, regardless of its setting. A leading or trailing quote is loaded as part of CLOB data. Two consecutive quotes between enclosing quotes are loaded as two consecutive quotes with the QUOTES ON option.
- SAP ASE BCP does not support the QUOTES option. All field data is copied in or out equivalent to the QUOTES OFF setting. As QUOTES ON is the default setting for the SAP IQ LOAD TABLE statement, you must specify QUOTES OFF when importing ASE data from BCP output to an SAP IQ table.
Exceptions:
- If LOAD TABLE encounters any nonwhite characters after the ending quote character for an enclosed field, this error is reported and the load operation is rolled back:
  Non-SPACE text found after ending quote character for an enclosed field. SQLSTATE: QTA14 SQLCODE: -1005014L
- With QUOTES ON, if a single or double quote is specified as the first character of the column delimiter, an error is reported and the load operation fails:
  Single or double quote mark cannot be the 1st character of column delimiter or row terminator with QUOTES option ON. SQLSTATE: QCA90 SQLCODE: -1013090L

ESCAPES if you omit a <column-spec> definition for an input field and ESCAPES is ON (the default), characters following the backslash character are recognized and interpreted as special characters by the database server. You can include newline characters as the combination \n, and other characters as hexadecimal ASCII codes, such as \x09 for the tab character. A sequence of two backslash characters ( \\ ) is interpreted as a single backslash. For SAP IQ, you must set ESCAPES OFF.

FORMAT
SAP IQ supports ASCII and binary input fields. The format is usually defined by the <column-spec> described above. If you omit that definition for a column, by default SAP IQ uses the format defined by this option. Input lines are assumed to have ascii (the default) or binary fields, one row per line, with values separated by the column delimiter character.
SAP IQ also accepts data from BCP character files as input to the LOAD TABLE command.
- The BCP data file loaded into SAP IQ tables using the LOAD TABLE FORMAT BCP statement must be exported (BCP OUT) in cross-platform file format using the -c option.
- For FORMAT BCP, the default column delimiter for the LOAD TABLE statement is <tab> and the default row terminator is <newline>.
- For FORMAT BCP, the last column in a row must be terminated by the row terminator, not by the column delimiter. If the column delimiter is present before the row terminator, then the column delimiter is treated as a part of the data.
- Data for columns that are not the last column in the load specification must be delimited by the column delimiter only. If a row terminator is encountered before a column delimiter for a column that is not the last column, then the row terminator is treated as a part of the column data.
- Column delimiter can be specified via the DELIMITED BY clause. For FORMAT BCP, the delimiter must be less than or equal to 10 characters in length. An error is returned, if the delimiter length is more than 10.
- For FORMAT BCP, the load specification may contain only column names, NULL, and ENCRYPTED. An error is returned, if any other option is specified in the load specification.
  For example, these LOAD TABLE load specifications are valid:
  LOAD TABLE x( c1, c2 null(blanks), c3 ) FROM 'bcp_file.bcp' FORMAT BCP ...
  LOAD TABLE x( c1 encrypted(bigint,'KEY-ONE','aes'), c2, c3 ) FROM 'bcp_file.bcp' FORMAT BCP ...

DELIMITED BY
if you omit a column delimiter in the <column-spec> definition, the default column delimiter character is a comma. You can specify an alternative column delimiter by providing a single ASCII character or the hexadecimal character representation. The DELIMITED BY clause is:
```
... DELIMITED BY '\x09' ...
```
To use the newline character as a delimiter, you can specify either the special combination '\n' or its ASCII value '\x0a'. Although you can specify up to four characters in the column-spec <delimiter-string>, you can specify only a single character in the DELIMITED BY clause.

STRIP
determines whether unquoted values should have trailing blanks stripped off before they are inserted. The LOAD TABLE command accepts these STRIP keywords:
- STRIP OFF do not strip off trailing blanks.
- STRIP RTRIM strip trailing blanks.
- STRIP ON deprecated. Use STRIP RTRIM.
With STRIP turned on (the default), SAP IQ strips trailing blanks from values before inserting them. This is effective only for VARCHAR data. STRIP OFF preserves trailing blanks.

Trailing blanks are stripped only for unquoted strings. Quoted strings retain their trailing blanks. If you do not require blank sensitivity, you can use the FILLER option as an alternative to be more specific in the number of bytes to strip, instead of all the trailing spaces. STRIP OFF is more efficient for SAP IQ, and it adheres to the ANSI standard when dealing with trailing blanks. (CHAR data is always padded, so the STRIP option only affects VARCHAR data.)

The STRIP option applies only to variable-length non-binary data and does not apply to ASCII fixed-width inserts. For example, assume this schema:
```
CREATE TABLE t( c1 VARCHAR(3) );
LOAD TABLE t( c1 ',' ) ........ STRIP RTRIM    // trailing blanks trimmed

LOAD TABLE t( c1 ',' ) ........ STRIP OFF      // trailing blanks not trimmed

LOAD TABLE t( c1 ASCII(3) ) ... STRIP RTRIM    // trailing blanks not trimmed
LOAD TABLE t( c1 ASCII(3) ) ... STRIP OFF      // trailing blanks trimmed

LOAD TABLE t( c1 BINARY ) ..... STRIP RTRIM    // trailing blanks trimmed
LOAD TABLE t( c1 BINARY ) ..... STRIP OFF      // trailing blanks trimmed
```
Trailing blanks are always trimmed from binary data.

WITH CHECKPOINT determines whether SAP IQ performs a checkpoint. This option is useful only when loading SAP SQL Anywhere tables in an SAP IQ database.
The default setting is OFF. If this clause is set to ON, a checkpoint is issued after successfully completing and logging the statement. If the server fails after a connection commits and before the next checkpoint, the data file used to load the table must be present for the recovery to complete successfully. However, if WITH CHECKPOINT ON is specified, and recovery is subsequently required, the data file need not be present at the time of recovery.

The data files are required, regardless of what is specified for this clause, if the database becomes corrupt and you need to use a backup and apply the current log file.

Caution If you set the database option CONVERSION_ERROR to OFF, you may load bad data into your table without any error being reported. If you do not specify WITH CHECKPOINT ON, and the database needs to be recovered, the recovery may fail as CONVERSION_ERROR is ON (the default value) during recovery. It is recommended that you do not load tables when CONVERSION_ERROR is set to OFF and WITH CHECKPOINT ON is not specified.
See also CONVERSION_ERROR Option [TSQL].

BYTE ORDER
specifies the byte order during reads. This option applies to all binary input fields. If none are defined, this option is ignored. SAP IQ always reads binary data in the format native to the machine it is running on (default is NATIVE). You can also specify:
- HIGH when multibyte quantities have the high order byte first (for big endian platforms like Sun, IBM AIX, and HP).
- LOW when multibyte quantities have the low order byte first (for little endian platforms like Windows).

LIMIT specifies the maximum number of rows to insert into the table. The default is 0 for no limit. The maximum is 2³¹ - 1 (2147483647) rows.

NOTIFY specifies that you be notified with a message each time the specified number of rows is successfully inserted into the table. The default is 0, meaning no notifications are printed. The value of this option overrides the value of the NOTIFY_MODULUS database option.

ON FILE ERROR
specifies the action SAP IQ takes when an input file cannot be opened because it does not exist or you have incorrect privileges to read the file. You can specify one of the following:
- ROLLBACK aborts the entire transaction (the default).
- FINISH finishes the insertions already completed and ends the load operation.
- CONTINUE returns an error but only skips the file to continue the load operation.
Only one ON FILE ERROR clause is permitted.

PREVIEW displays the layout of input into the destination table including starting position, name, and data type of each column. SAP IQ displays this information at the start of the load process. If you are writing to a log file, this information is also included in the log.

ROW DELIMITED BY delimiter-string
specifies a string up to 4 bytes in length that indicates the end of an input record. You can use this option only if all fields within the row are any of the following:
- Delimited with column terminators
- Data defined by the DATE or DATETIME <column-spec> options
- ASCII fixed length fields
Always include ROW DELIMITED BY to insure parallel loads. Omitting this clause from the LOAD specification may cause SAP IQ to load serially rather than in parallel.
You cannot use this option if any input fields contain binary data. With this option, a row terminator causes any missing fields to be set to NULL. All rows must have the same row delimiters, and it must be distinct from all column delimiters. The row and field delimiter strings cannot be an initial subset of each other. For example, you cannot specify “*” as a field delimiter and “*#” as the row delimiter, but you could specify “#” as the field delimiter with that row delimiter.

If a row is missing its delimiters, SAP IQ returns an error and rolls back the entire load transaction. The only exception is the final record of a file where it rolls back that row and returns a warning message. On Windows, a row delimiter is usually indicated by the newline character followed by the carriage return character. You might need to specify this as the <delimiter-string> (see above for description) for either this option or FILLER.

SKIP defines the number of rows to skip at the beginning of the input tables for this load. The maximum number of rows to skip is 2³¹ - 1 (2147483647). The default is 0. SKIP runs in single-threaded mode as it reads the rows to skip.

HEADER SKIP…HEADER DELIMITED BY
specifies a number of lines at the beginning of the data file, including header rows, for LOAD TABLE to skip. All LOAD TABLE column specifications and other load options are ignored, until the specified number of rows is skipped.
- The number of lines to skip is greater than or equal to zero.
- Lines are determined by a 1 to 4 character delimiter string specified in the HEADER DELIMITED BY clause. The default HEADER DELIMITED BY string is the ‘\n’ character.
- The HEADER DELIMITED BY string has a maximum length of four characters. An error is returned, if the string length is greater than four or less than one.
- When a non-zero HEADER SKIP value is specified, all data inclusive of the HEADER DELIMITED BY delimiter is ignored, until the delimiter is encountered the number of times specified in the HEADER SKIP clause.
- All LOAD TABLE column specifications and other load options are ignored, until the specified number of rows has been skipped. After the specified number of rows has been skipped, the LOAD TABLE column specifications and other load options are applied to the remaining data.
- The "header" bytes are ignored only at the beginning of the data. When multiple files are specified in the USING clause, HEADER SKIP only ignores data starting from the first row of the first file, until it skips the specified number of header rows, even if those rows exist in subsequent files. LOAD TABLE does not look for headers once it starts parsing actual data.
- No error is reported, if LOAD TABLE processes all input data before skipping the number of rows specified by HEADER SKIP.

WORD SKIP
allows the load to continue when it encounters data longer than the limit specified when the word index was created.
If a row is not loaded because a word exceeds the maximum permitted size, a warning is written to the .iqmsg file. WORD size violations can be optionally logged to the MESSAGE LOG file and rejected rows logged to the ROW LOG file specified in the LOAD TABLE statement.
- If the option is not specified, LOAD TABLE reports an error and rolls back on the first occurrence of a word that is longer than the specified limit.
- <number> specifies the number of times the “Words exceeding the maximum permitted word length not supported” error is ignored.
- 0 (zero) means there is no limit.

ON PARTIAL INPUT ROW
specifies the action to take when a partial input row is encountered during a load. You can specify one of the following:
- CONTINUE issues a warning and continues the load operation. This is the default.
- ROLLBACK aborts the entire load operation and reports the error.
```
Partial input record skipped at EOF.
SQLSTATE: QDC32    SQLSTATE: -1000232L
```

IGNORE CONSTRAINT
specifies whether to ignore CHECK, UNIQUE, NULL, DATA VALUE, and FOREIGN KEY integrity constraint violations that occur during a load and the maximum number of violations to ignore before initiating a rollback. Specifying each <constrainttype> has the following result:
- CHECK limit if <limit> specifies zero, the number of CHECK constraint violations to ignore is infinite. If CHECK is not specified, the first occurrence of any CHECK constraint violation causes the LOAD statement to roll back. If <limit> is nonzero, then the <limit> +1 occurrence of a CHECK constraint violation causes the load to roll back.
- UNIQUE <limit> if <limit> specifies zero, then the number of UNIQUE constraint violations to ignore is infinite. If <limit> is nonzero, then the <limit> +1 occurrence of a UNIQUE constraint violation causes the load to roll back.
- NULL <limit> if <limit> specifies zero, then the number of NULL constraint violations to ignore is infinite. If <limit> is nonzero, then the <limit> +1 occurrence of a NULL constraint violation causes the load to roll back.
- FOREIGN KEY <limit> if <limit> specifies zero, the number of FOREIGN KEY constraint violations to ignore is infinite. If <limit> is nonzero, then the <limit> +1 occurrence of a FOREIGN KEY constraint violation causes the load to roll back.
- DATA VALUE <limit> f the database option CONVERSION_ERROR = ON, an error is reported and the statement rolls back. If <limit> specifies zero, then the number of DATA VALUE constraint violations (data type conversion errors) to ignore is infinite. If <limit> is nonzero, then the <limit> +1 occurrence of a DATA VALUE constraint violation causes the load to roll back.
- ALL <limit>
  if the database option CONVERSION_ERROR = ON, an error is reported and the statement rolls back. If <limit> specifies zero, then the cumulative total of all integrity constraint violations to ignore is infinite. If <limit> is nonzero, then load rolls back when the cumulative total of all ignored UNIQUE, NULL, DATA VALUE, and FOREIGN KEY integrity constraint violations exceeds the value of <limit>. For example, you specify this IGNORE CONSTRAINT option:
  IGNORE CONSTRAINT NULL 50, UNIQUE 100, ALL 200
  The total number of integrity constraint violations cannot exceed 200, whereas the total number of NULL and UNIQUE constraint violations cannot exceed 50 and 100, respectively. Whenever any of these limits is exceeded, the LOAD TABLE statement rolls back.
  
  Note
  A single row can have more than one integrity constraint violation. Every occurrence of an integrity constraint violation counts towards the limit of that type of violation.
  
  Set the IGNORE CONSTRAINT option limit to a nonzero value if you are logging the ignored integrity constraint violations. Logging an excessive number of violations affects the performance of the load
If CHECK, UNIQUE, NULL, or FOREIGN KEY is not specified in the IGNORE CONSTRAINT clause, then the load rolls back on the first occurrence of each of these types of integrity constraint violation.

If DATA VALUE is not specified in the IGNORE CONSTRAINT clause, then the load rolls back on the first occurrence of this type of integrity constraint violation, unless the database option CONVERSION_ERROR = OFF. If CONVERSION_ERROR = OFF, a warning is reported for any DATA VALUE constraint violation and the load continues.

When the load completes, an informational message regarding integrity constraint violations is logged in the .iqmsg file. This message contains the number of integrity constraint violations that occurred during the load and the number of rows that were skipped.

MESSAGE LOG specifies the names of files in which to log information about integrity constraint violations and the types of violations to log. Timestamps indicating the start and completion of the load are logged in both the MESSAGE LOG and the ROW LOG files. Both MESSAGE LOG and ROW LOG must be specified, or no information about integrity violations is logged.

If the ONLY LOG clause is not specified, no information on integrity constraint violations is logged. Only the timestamps indicating the start and completion of the load are logged.
Information is logged on all integrity constraint-type violations specified in the ONLY LOG clause or for all word index-length violations if the keyword WORD is specified.
If constraint violations are being logged, every occurrence of an integrity constraint violation generates exactly one row of information in the MESSAGE LOG file.
The number of rows (errors reported) in the MESSAGE LOG file can exceed the IGNORE CONSTRAINT option limit, because the load is performed by multiple threads running in parallel. More than one thread might report that the number of constraint violations has exceeded the specified limit.
If constraint violations are being logged, exactly one row of information is logged in the ROW LOG file for a given row, regardless of the number of integrity constraint violations that occur on that row.
The number of distinct errors in the MESSAGE LOG file might not exactly match the number of rows in the ROW LOG file. The difference in the number of rows is due to the parallel processing of the load described above for the MESSAGE LOG.
The MESSAGE LOG and ROW LOG files cannot be raw partitions or named pipes.
If the MESSAGE LOG or ROW LOG file already exists, new information is appended to the file.
Specifying an invalid file name for the MESSAGE LOG or ROW LOG file generates an error.
Specifying the same file name for the MESSAGE LOG and ROW LOG files generates an error.

Various combinations of the IGNORE CONSTRAINT and MESSAGE LOG options result in different logging actions.

Table 1: LOAD TABLE Logging Actions
IGNORE CONSTRAINT Specified?	MESSAGE LOG Specified?	Action
yes	yes	All ignored integrity constraint violations are logged, including the user specified limit, before the rollback.
no	yes	The first integrity constraint violation is logged before the rollback.
yes	no	Nothing is logged.
no	no	Nothing is logged. The first integrity constraint violation causes a rollback.

Tip Set the IGNORE CONSTRAINT option limit to a nonzero value, if you are logging the ignored integrity constraint violations. If a single row has more than one integrity constraint violation, a row for each violation is written to the MESSAGE LOG file. Logging an excessive number of violations affects the performance of the load.

LOG DELIMITED BY
specifies the separator between data values in the ROW LOG file. The default separator is a comma.
SAP IQ no longer returns an error message when FORMAT BCP is specified as a LOAD TABLE clause. In addition, these conditions are verified and proper error messages are returned
- If the specified load format is not ASCII, BINARY, or BCP, SAP IQ returns the message “Only ASCII, BCP and BINARY are supported LOAD formats.”
- If the LOAD TABLE column specification contains anything other than column name, NULL, or ENCRYPTED, then SAP IQ returns the error message “Invalid load specification for LOAD ... FORMAT BCP.”
- If the column delimiter or row terminator size for the FORMAT BCP load is greater than 10 characters, then SAP IQ returns the message “Delimiter ‘%2’ must be 1 to %3 characters in length.” (where %3 equals 10).
  Messages corresponding to error or warning conditions which can occur for FORMAT BCP as well as FORMAT ASCII are the same for both formats.
- If the load default value specified is AUTOINCREMENT, IDENTITY, or GLOBAL AUTOINCREMENT, SAP IQ returns the error “Default value %2 cannot be used as a LOAD default value. %1”
- If the LOAD TABLE specification does not contain any columns that need to be loaded from the file specified, SAP IQ returns the error “The LOAD statement must contain at least one column to be loaded from input file.” and the LOAD TABLE statement rolls back.
- If a load exceeds the limit on the maximum number of terms for a text document with TEXT indexes, SAP IQ returns the error “Text document exceeds maximum number of terms. Support up to 4294967295 terms per document.”

Examples

(back to top)

Example 1 load data from one file into the Products table on a Windows system. A tab is used as the column delimiter following the Description and Color columns:

LOAD TABLE Products
( ID ASCII(6),
FILLER(1),
Name   ASCII(15),
FILLER(1),
Description   '\x09',
Size   ASCII(2),
FILLER(1),
Color   '\x09',
Quantity   PREFIX 2,
UnitPrice   PREFIX 2,
FILLER(2) )
FROM 'C:\\mydata\\source1.dmp'
QUOTES OFF
ESCAPES OFF
BYTE ORDER LOW
NOTIFY 1000

Example 2 load data from a file a.inp on a client computer:

LOAD TABLE t1(c1,c2,filler(30))
USING CLIENT FILE 'c:\\client-data\\a.inp'
QUOTES OFF ESCAPES OFF
IGNORE CONSTRAINT UNIQUE 0, NULL 0
MESSAGE LOG 'c:\\client-data\\m.log'
ROW LOG 'c:\\client-data\\r.log'ONLY LOG UNIQUE

Example 3 load data from two files into the product_new table (which allows NULL values) on a UNIX system. The tab character is the default column delimiter, and the newline character is the row delimiter:

LOAD TABLE product_new
( id,
name,
description,
size,
color   '\x09'   NULL( 'null', 'none', 'na' ),
quantity   PREFIX 2,
unit_price   PREFIX 2 )
FROM '/s1/mydata/source2.dump',
'/s1/mydata/source3.dump'
QUOTES OFF
ESCAPES OFF
FORMAT ascii
DELIMITED BY '\x09'
ON FILE ERROR CONTINUE
ROW DELIMITED BY '\n'

Example 4 ignore 10 word-length violations; on the 11th, deploy the new error and roll back the load:

load table PTAB1(
       ck1         ','  null ('NULL') ,
       ck3fk2c2    ','  null ('NULL') ,
       ck4         ','  null ('NULL') ,
       ck5         ','  null ('NULL') ,
       ck6c1       ','  null ('NULL') ,
       ck6c2       ','  null ('NULL') ,
       rid         ','  null ('NULL')  )
FROM 'ri_index_selfRI.inp'
       row delimited by '\n'
       LIMIT 14   SKIP 10
       IGNORE CONSTRAINT UNIQUE 2, FOREIGN KEY 8
       word skip 10 quotes off escapes off strip
       off

Example 5
load data into table t1 from the BCP character file bcp_file.bcp using the FORMAT BCP load option:
```
LOAD TABLE t1 (c1, c2, c3)
FROM ‘bcp_file.bcp’
FORMAT BCP
...
```

Example 6 load default values 12345 into c1 using the DEFAULT load option, and load c2 and c3 with data from the LoadConst04.dat file:

LOAD TABLE t1 (c1 DEFAULT ‘12345 ’, c2, c3, filler(1))
FROM ‘LoadConst04.dat’
STRIP OFF
QUOTES OFF
ESCAPES OFF
DELIMITED BY ‘,’;

Example 7
load c1 and c2 with data from the file bcp_file.bcp using the FORMAT BCP load option and set c3 to the value 10:
```
LOAD TABLE t1 (c1, c2, c3 DEFAULT ‘10’)
FROM ‘bcp_file.bcp’
FORMAT BCP
QUOTES OFF
ESCAPES OFF;
```
Example 8
this code fragment ignores one header row at the beginning of the data file, where the header row is delimited by ‘&&’:
```
LOAD TABLE
...HEADER SKIP 1 HEADER DELIMITED by '&&'
```
Example 9
this code fragment ignores 2 header rows at the beginning of the data file, where each header row is delimited by ‘\n’:
```
LOAD TABLE
...HEADER SKIP 2
```
Example 10
load a file into a RLV-enabled table.
Load data into RLV-enabled table rvt1 from the BCP character file bcp_file.bcp using the FORMAT BCP load option:
```
LOAD TABLE rvt1 (c1, c2, c3)
FROM ‘bcp_file.bcp’
FORMAT BCP
...
```

Usage

(back to top)

The LOAD TABLE statement allows efficient mass insertion into a database table from a file with ASCII or binary data.

The LOAD TABLE options also let you control load behavior when integrity constraints are violated and to log information about the violations.

You can use LOAD TABLE on a temporary table, but the temporary table must have been declared with ON COMMIT PRESERVE ROWS, or the next COMMIT removes the rows you have loaded.

LOAD TABLE supports loading of large object (LOB) data.

SAP IQ supports loading from both ASCII and binary data, and it supports both fixed- and variable-length formats. To handle all of these formats, you must supply a <load-specification> to tell SAP IQ what kind of data to expect from each “column” or field in the source file. The <column-spec> lets you define these formats:

ASCII with a fixed length of bytes. The <input-width> value is an integer indicating the fixed width in bytes of the input field in every record.
Binary or non-binary fields that use a number of PREFIX bytes (1, 2, or 4) to specify the length of the input.
There are two parts related to a PREFIX clause:
- Prefix value – always a binary value.
- Associated data bytes – always character format; never binary format.
If the data is unloaded using the extraction facility with the TEMP_EXTRACT_BINARY option set ON, you must use the BINARY WITH NULL BYTE parameter for each column when you load the binary data.
Variable-length characters delimited by a separator. You can specify the terminator as hexadecimal ASCII characters. The <delimiter-string> can be any string of up to 4 characters, including any combination of printable characters, and any 8-bit hexadecimal ASCII code that represents a nonprinting character. For example, specify:
- '\x09' to represent a tab as the terminator.
- '\x00' for a null terminator (no visible terminator as in “C” strings).
- '\x0a' for a newline character as the terminator. You can also use the special character combination of '\n' for newline.
Note
The delimiter string can be from 1 to 4 characters long, but you can specify only a single character in the DELIMITED BY clause. For BCP, the delimiter can be up to 10 characters.

DATE or DATETIME string as ASCII characters. You must define the <input-date-format> or <input-datetime-format> of the string using one of the corresponding formats for the date and datetime data types supported by SAP IQ. Use DATE for date values and DATETIME for datetime and time values.

Table 2: Formatting Dates and Times
Option	Meaning
yyyy or YYYY yy or YY	Represents number of year. Default is current year.
mm or MM	Represents number of month. Always use leading zero or blank for number of the month where appropriate, for example, '05' for May. `DATE` value must include a month. For example, if the DATE value you enter is 1998, you receive an error. If you enter '03', SAP IQ applies the default year and day and converts it to '1998-03-01'.
dd or DD jjj or JJJ	Represents number of day. Default day is 01. Always use leading zeros for number of day where appropriate, for example, '01' for first day. J or j indicates a Julian day (1 to 366) of the year.
hh HH	Represents hour. Hour is based on 24-hour clock. Always use leading zeros or blanks for hour where appropriate, for example, '01' for 1 am. '00' is also valid value for hour of 12 a.m.
nn	Represents minute. Always use leading zeros for minute where appropriate, for example, '08' for 8 minutes.
ss[.ssssss]	Represents seconds and fraction of a second.
aa	Represents the a.m. or p.m. designation.
pp	Represents the p.m. designation only if needed. (This is an incompatibility with SAP IQ versions earlier than 12.0; previously, “pp” was synonymous with “aa”.)
hh	SAP IQ assumes zero for minutes and seconds. For example, if the DATETIME value you enter is '03', SAP IQ converts it to '03:00:00.0000'.
hh:nn or hh:mm	SAP IQ assumes zero for seconds. For example, if the time value you enter is '03:25', SAP IQ converts it to '03:25:00.0000'.

Table 3: Sample DATE and DATETIME Format Options
Input data	Format specification
12/31/98	DATE ('MM/DD/YY')
19981231	DATE ('YYYYMMDD')
123198140150	DATETIME ('MMDDYYhhnnss')
14:01:50 12-31-98	DATETIME ('hh:nn:ss MM-DD-YY')
18:27:53	DATETIME ('hh:nn:ss')
12/31/98 02:01:50AM	DATETIME ('MM/DD/YY hh:nn:ssaa')

SAP IQ has built-in load optimizations for common date, time, and datetime formats. If your data to be loaded matches one of these formats, you can significantly decrease load time by using the appropriate format.

You can also specify the date/time field as an ASCII fixed-width field (as described above) and use the FILLER(1) option to skip the column delimiter.

The NULL portion of the <column-spec> indicates how to treat certain input values as NULL values when loading into the table column. These characters can include BLANKS, ZEROS, or any other list of literals you define. When specifying a NULL value or reading a NULL value from the source file, the destination column must be able to contain NULLs.

ZEROS are interpreted as follows: the cell is set to NULL if (and only if) the input data (before conversion, if ASCII) is all binary zeros (and not character zeros).

If the input data is character zero, then:
1. NULL (ZEROS) never causes the cell to be NULL.
2. NULL ('0') causes the cell to be NULL.
If the input data is binary zero (all bits clear), then:
1. NULL (ZEROS) causes the cell to be NULL.
2. NULL ('0') never causes the cell to be NULL.

For example, if your LOAD statement includes col1 date('yymmdd') null(zeros) and the date is 000000, you receive an error indicating that 000000 cannot be converted to a DATE(4). To get LOAD TABLE to insert a NULL value in col1 when the data is 000000, either write the NULL clause as null('000000'), or modify the data to equal binary zeros and use NULL(ZEROS).

If the length of a VARCHAR cell is zero and the cell is not NULL, you get a zero-length cell. For all other data types, if the length of the cell is zero, SAP IQ inserts a NULL. This is ANSI behavior. For non-ANSI treatment of zero-length character data, set the NON_ANSI_NULL_VARCHAR database option.

Use the DEFAULT option to specify a load default column value. You can load a default value into a column, even if the column does not have a default value defined in the table schema. This feature provides more flexibility at load time.

The LOAD TABLE DEFAULTS option must be ON in order to use the default value specified in the LOAD TABLE statement. If the DEFAULTS option is OFF, the specified load default value is not used and a NULL value is inserted into the column instead.
The LOAD TABLE command must contain at least one column that needs to be loaded from the file specified in the LOAD TABLE command. Otherwise, an error is reported and the load is not performed.
The specified load default value must conform to the supported default values for columns and default value restrictions. The LOAD TABLE DEFAULT option does not support AUTOINCREMENT, IDENTITY, or GLOBAL AUTOINCREMENT as a load default value.
The LOAD TABLE DEFAULT <default-value> must be of the same character set as that of the database.
Encryption of the default value is not supported for the load default values specified in the LOAD TABLE DEFAULT clause.
A constraint violation caused by evaluation of the specified load default value is counted for each row that is inserted in the table.

Another important part of the <load-specification> is the FILLER option. This option indicates you want to skip over a specified field in the source input file. For example, there may be characters at the end of rows or even entire fields in the input files that you do not want to add to the table. As with the <column-spec> definition, FILLER specifies ASCII fixed length of bytes, variable length characters delimited by a separator, and binary fields using PREFIX bytes.

Standards

(back to top)

SQL—Vendor extension to ISO/ANSI SQL grammar.
SAP Database products—Not applicable.

Permissions

(back to top)

The privileges required to execute a LOAD TABLE statement depend on the database server -gl command line option, as follows:

-gl ALL
(default) you must have one of:
- You are the owner of the table
- ALTER object-level privilege on the table
- LOAD object-level privilege on the table
- ALTER ANY TABLE system privilege
- LOAD ANY TABLE system privilege
- ALTER ANY OBJECT system privilege

-gl DBA
you must have one of these system privileges:
- ALTER ANY TABLE
- LOAD ANY TABLE
- ALTER ANY OBJECT

-gl NONE execution of the LOAD TABLE statement is not permitted.

For more information on the -gl command line option, please refer Utility Guide > start_iq Database Server Startup Utility > start_iq Server Options.

LOAD TABLE also requires a write lock on the table.

When using the USING CLIENT FILE clause:

READ CLIENT FILE system privilege is also required.
Read privileges are required on the directory being read from.
The ALLOW_READ_CLIENT_FILE database option must be enabled.
The ALLOW_READ_CLIENT_FILE secure feature must be enabled.