LOAD TABLE supports BCP files as input [CR 437332]

Sybase IQ 12.7 ESD #2 supports using a BCP character file as input to the LOAD TABLE command. The BCP input file must be generated by the BCP OUT command with the -c option. The LOAD TABLE FORMAT BCP feature is an alternative to the iq_bcp utility and will perform better than iq_bcp in all cases.

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 | bcp } ]
... [ DELIMITED BY 'string' ]
... 

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’ ] )

The following LOAD TABLE statement is now supported with the FORMAT BCP load option:

LOAD TABLE t1 (c1, c2, c3)
FROM ‘bcp_file.bcp’
FORMAT BCP
...

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. Files generated by the iq_bcp utility (using the -c option) can be used as input to the LOAD TABLE FORMAT BCP command.

• 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, the following 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 Encrypted Columns in Sybase IQ.

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

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