LOAD TABLE supports loading default values [CR 396843]

Sybase IQ 12.7 ESD #4 supports loading a default value into a column as specified in the LOAD TABLE statement. You can now load a default value into a column, even if the column does not have a default value defined in the table schema. This feature carries the performance benefits of the LOAD TABLE statement and provides the user with more flexibility at load time.

The LOAD TABLE syntax includes a new DEFAULT clause in the column specification.

LOAD [ INTO ] TABLE [ owner.]table-name
... ( load-specification [, …] )
... FROM { 'filename-string' | filename-variable } [, …]
... [ CHECK CONSTRAINTS { ON | OFF } ]
... [ DEFAULTS { ON | OFF } ]
... QUOTES OFF
... ESCAPES OFF
... [ FORMAT { ascii | binary } ]
... [ DELIMITED BY 'string' ]
... [ STRIP { ON | OFF } ]
.
.
.

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

The following LOAD TABLE statements are now supported with the DEFAULT load clause:

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

LOAD TABLE t1 (c1, c2, c3 DEFAULT ‘10’)
FROM ‘bcp_file.bcp’
DEFAULTS ON
FORMAT BCP
QUOTES OFF
ESCAPES OFF;

The LOAD TABLE DEFAULT value specification 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 Sybase IQ System Administration Guide. The LOAD TABLE DEFAULT value specification does not support AUTOINCREMENT, IDENTITY, or GLOBAL AUTOINCREMENT as a load default value.

The LOAD TABLE DEFAULT value specification 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 value specification.

A constraint violation caused by evaluation of the specified load default value is counted for each row that is inserted in the table.

Sybase IQ no longer returns an error message when FORMAT BCP is specified as a LOAD TABLE clause.

• 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.