Imports data into a database table from an external file.
LOAD [ 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 { ON | 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 ]
... [ WORD SKIP number ]
... [ START ROW ID number ]
... [ ON PARTIAL INPUT ROW { ROLLBACK | CONTINUE } ]
... [ IGNORE CONSTRAINT constrainttype [, …] ]
... [ MESSAGE LOG ‘string’ ROW LOG ‘string’ [ ONLY LOG logwhat [, …] ]
... [ LOG DELIMITED BY ‘string’ ]
{ column-name [ column-spec ] | FILLER ( filler-type ) }
{ 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', …} ) ]
{ input-width | PREFIX { 1 | 2 | 4 } | 'delimiter-string' }
{ CHECK integer | UNIQUE integer | NULL integer | FOREIGN KEY integer | DATA VALUE integer | ALL integer }
{ CHECK | ALL | NULL | UNIQUE | DATA VALUE | FOREIGN KEY | WORD }
Loads 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
Loads 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
Loads 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'
Ignores 10 word-length violations; on the 11th, deploys the new error and rolls 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
Loads data into table t1from 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 ...
This LOAD TABLE statement loads default values 12345 into c1 using the DEFAULT load option, and loads 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 ‘,’;
This LOAD TABLE statement loads c1 and c2 with data from the file bcp_file.bcp using the FORMAT BCP load option, and sets c3 to the value 10.
LOAD TABLE t1 (c1, c2, c3 DEFAULT ‘10’) FROM ‘bcp_file.bcp’ FORMAT BCP QUOTES OFF ESCAPES OFF;
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.
You can also specify more than one file to load data. In the FROM clause, specify each filename-string separated by commas. Because of resource constraints, Sybase 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.
When loading a multiplex database, use absolute (fully
qualified) paths in all file names. Do not use relative path names.
LOAD TABLE supports loading of large object (LOB) data. See Unstructured Data Analytics in Sybase IQ.
Sybase 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 Sybase 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 value 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.
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.
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 Sybase IQ. Use DATE for date values and DATETIME for datetime and time values.
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', Sybase 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 Sybase IQ versions earlier than 12.0; previously, “pp” was synonymous with “aa”.) |
hh |
Sybase IQ assumes zero for minutes and seconds. For example, if the DATETIME value you enter is '03', Sybase IQ converts it to '03:00:00.0000'. |
hh:nn or hh:mm |
Sybase IQ assumes zero for seconds. For example, if the time value you enter is '03:25', Sybase IQ converts it to '03:25:00.0000'. |
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:mm:ss MM-DD-YY') |
18:27:53 |
DATETIME ('hh:mm:ss') |
12/31/98 02:01:50AM |
DATETIME ('MM/DD/YY hh:mm:ssaa') |
Sybase 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. For a list of these formats, and details about optimizing performance when loading date and datetime data, see Chapter 7, “Moving Data In and Out of Databases” in the System Administration Guide: Volume 1.
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. For more information about specifying date and time data, see Date and time data types in Chapter 3, “SQL Data Types” in Reference: Building Blocks, Tables, and Procedures or Chapter 7, “Moving Data In and Out of Databases” in the System Administration Guide: Volume 1.
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:
NULL (ZEROS) never causes the cell to be NULL.
NULL ('0') causes the cell to be NULL.
If the input data is binary zero (all bits clear), then:
NULL (ZEROS) causes the cell to be NULL.
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, Sybase 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 as described in the section “Using column defaults,” in Chapter 9, “Ensuring Data Integrity,” of the System Administration Guide: Volume 1. 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 lets you specify ASCII fixed length of bytes, variable length characters delimited by a separator, and binary fields using PREFIX bytes.
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. In particular:
To indicate directory paths in Windows systems, 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.
Descriptions of each statement clause follow:
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. Sybase 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.
When bulk loading large objects, the USING CLIENT FILE clause applies to both primary and secondary files. (If you have the Unstructured Data Analytics Option, see Unstructured Data Analytics in Sybase IQ for details.)
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, the ALLOW_READ_CLIENT_FILE database option, and/or the READCLIENTFILE access control.
See ”Client-side data security” and ”Accessing data on client computers” in SQL Anywhere Server – SQL Usage.
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
CHECK CONSTRAINTS This option defaults to ON. When you specify CHECK CONSTRAINTS ON, check constraints are evaluated and you are free to ignore or log them.
Setting CHECK CONSTRAINTS OFF causes Sybase 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 If the DEFAULTS option is ON (the default) and the column has a default value, that value is used. 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.
For detailed information on the use of column DEFAULT values with loads and inserts, see “Using column defaults” in Chapter 9, “Ensuring Data Integrity” in the System Administration Guide: Volume 1.
QUOTES This parameter is optional and the default is ON. With QUOTES turned on, LOAD TABLE expects input strings to be enclosed in quote characters. 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. (See “STRIP” for more information.) Leading blank or TAB characters are trimmed only when the QUOTES 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 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:
The QUOTES ON option 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.
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.
Adaptive Server Enterprise 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 Sybase IQ LOAD TABLE statement, you must specify QUOTES OFF when importing ASE data from BCP output to a Sybase 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
For an example of the QUOTES option, see “Bulk loading data using the LOAD TABLE statement” in Chapter 7, “Moving Data In and Out of Databases” in the System Administration Guide: Volume 1.
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 Sybase IQ, you must set ESCAPES OFF.
FORMAT Sybase 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 Sybase 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.
For a detailed description of the binary format used by Sybase IQ to produce data files that can be read by the LOAD TABLE statement using the FORMAT BINARY and BINARY column specification clauses, see “Using binary load format” in Chapter 7, “Moving Data In and Out of Databases,” of System Administration Guide: Volume 1.
Sybase IQ also accepts data from BCP character files as input to the LOAD TABLE command.
The BCP data file loaded into Sybase 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 ...
For information on the LOAD TABLE ENCRYPTED clause, see Advanced Security in Sybase IQ.
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 as follows:
... 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 The STRIP clause specifies whether unquoted values should have trailing blanks stripped off before they are inserted. The LOAD TABLE command accepts these STRIP keywords:
With STRIP turned on (the default), Sybase 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 Sybase 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 The default setting is OFF. If set to ON, a checkpoint is issued after successfully completing and logging the statement.
If you do not specify WITH CHECKPOINT ON, the file used for loading must be retained in case recovery is required. When you specify WITH CHECKPOINT ON, a checkpoint is carried out after loading, and recovery is guaranteed even if the data file is then removed from the system.
BYTE ORDER Specifies the byte order during reads. This option applies to all binary input fields. If none are defined, this option is ignored. Sybase 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 231 - 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 every 100,000 rows. The value of this option overrides the value of the NOTIFY_MODULUS database option.
ON FILE ERROR Specifies the action Sybase IQ takes when an input file cannot be opened because it does not exist or you have incorrect permissions 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. Sybase 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 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
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, Sybase 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 231 - 1 (2147483647). The default is 0.
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.
START ROW ID Specifies the record identification number of a row in the Sybase IQ table where it should start inserting.
The START ROW ID clause of the LOAD TABLE and the INSERT commands is not allowed on a partitioned table.
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 If 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.
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.
Sybase strongly recommends setting 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, as indicated in Table 1-10.
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. |
Sybase strongly recommends setting 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.
For more details on the contents and format of the MESSAGE LOG and ROW LOG files, see “Bulk loading data using the LOAD TABLE statement” in Chapter 7, “Moving Data In and Out of Databases” in the System Administration Guide: Volume 1.
Sybase 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, Sybase IQ now 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 Sybase IQ returns the new 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 Sybase 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, the error “Default
value %2 cannot be used as a LOAD default value. %1” is
reported.
If the LOAD TABLE specification
does not contain any columns that need to be loaded from the file
specified, the error “The LOAD statement must
contain at least one column to be loaded from input file.” is
reported and the LOAD TABLE statement rolls back.
LOAD TABLE with one or more text
indexes have the maximum number of terms for a text document. If
a text document, column where a text index is on, exceeds the limit.
an error “Text document exceeds maximum number
of terms. Support up to 4294967295 terms per document.” is
reported.
None
The permissions required to execute a LOAD TABLE statement depend on the database server -gl command line option, as follows:
If the -gl option is set to ALL, you must be the owner of the table, have DBA authority, or have ALTER permission.
If the -gl option is set to DBA, you must have DBA authority.
If the -gl option is set to NONE, LOAD TABLE is not permitted.
For more information, see the -gl command line option in “start_iq server options” in Chapter 1, “Running the Database Server” in the Utility Guide.
LOAD TABLE also requires an exclusive lock on the table.
“LOAD_ZEROLENGTH_ASNULL option”
“NON_ANSI_NULL_VARCHAR option”
“Bulk loading data using the LOAD TABLE statement” in Chapter 7, “Moving Data In and Out of Databases” in the System Administration Guide: Volume 1
“Monitoring disk space usage,” Chapter 14, “Troubleshooting Hints,” in the System Administration Guide: Volume 1
