LOAD TABLE Statement

Imports data into a database table from an external file.

Quick Links:

Go to Parameters

Go to Examples

Go to Usage

Go to Standards

Go to Permissions

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 KEYinteger 
   | DATA VALUE integer
   | ALL integer
   }

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

Parameters

(back to top)

Examples

(back to top)

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 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 SAP 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 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 Sybase IQ. Use DATE for date values and DATETIME for datetime and time values.
    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 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 SAP Sybase IQ versions earlier than 12.0; previously, “pp” was synonymous with “aa”.)
    hh SAP Sybase IQ assumes zero for minutes and seconds. For example, if the DATETIME value you enter is '03', SAP Sybase IQ converts it to '03:00:00.0000'.
    hh:nn or hh:mm SAP Sybase IQ assumes zero for seconds. For example, if the time value you enter is '03:25', SAP Sybase IQ converts it to '03:25:00.0000'.
    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 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.

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 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. 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 Sybase Database product—Not applicable.

Permissions

(back to top)

The permissions required to execute a LOAD TABLE statement depend on the database server -gl command line option, as follows:
  • -gl ALL – You must be the owner of the table, have ALTER or LOAD permission on the table, or have the ALTER ANY TABLE, LOAD ANY TALBE, or ALTER ANY OBJECT system privilege.
  • -gl DBA – You must have the ALTER ANY TABLE, LOAD ANY TABLE, or ALTER ANY OBJECT system privilege.
  • -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.

Parent topic: SQL Statements