Using the ASCII conversion option of LOAD TABLE

Use the ASCII conversion option to either:

• Convert ASCII input data to binary and specify the width of the input column so data can be read in correctly for that column, or

• Insert ASCII data into an ASCII data type column when the width of the input column is different from the width of the destination column. This option lets you specify how much of the input data it should read for each column.

You can use this option with any of the Sybase IQ data types, with 1, 2, or 4 prefix bytes, and with a column delimiter.

If the width of the input column is greater than the width of the destination column, Sybase IQ truncates the data upon insertion. If the width of the input data is less than the width of the destination column, for CHAR or VARCHAR data types Sybase IQ pads the data with spaces in the table upon insertion.

Variable width inserts to a VARCHAR column will not have trailing blanks trimmed, while fixed width inserts to a VARCHAR column will be trimmed. For example, assume that you are inserting into column varcolumn in a table called vartable. The following would constitute a fixed-width insert, where the value would not be trimmed because you explicitly say to include the two blanks (indicated by __ here):

INSERT INTO vartable VALUES ('box__')

If instead you inserted the same value from a flat file using delimited input, it would be a variable-width insert, and the trailing blanks would be trimmed.

The following table illustrates how the ASCII conversion option works with the Sybase IQ data types. The example inserts the data from the flat ASCII file shipinfo.t into the Sybase IQ table lineitem and summarizes the content and format of the input data and the table.

For the l_shipmode column, you insert ASCII data into an ASCII column (that has a VARCHAR data type). Notice the width of the two columns is different. In order for the insert on this column and the subsequent l_quantity column to be correct, you specify the width of the l_shipmode column so the correct amount of input data is read at the correct position.

For the l_quantity column, you are inserting ASCII data into a binary column (INT data type). In order for the insert on this column to be correct, you must convert the input data into binary and indicate the width of the input column.

The command for this is shown in the following UNIX example.

LOAD TABLE lineitem(
    l_shipmode ASCII(15),
    l_quantity ASCII(8),
FILLER(1))
FROM '/d1/MILL1/shipinfo.t'
PREVIEW ON