Loads That Specify Input Data Format

You can specify a wide range of load options that tell SAP Sybase IQ how to interpret and process the input file and what to do when errors occur.

You can specify load options in any order.

Example: Load That Displays Quotation Marks

Consider a table defined as:

CREATE TABLE t1 (c1 INT, c2 VARCHAR(20), c3 VARCHAR(20))

with the following input data:

1, apple , fruit1	 ,
2, "banana" , "fruit2",
3, " pear ", " fruit3 ",

Execute this query to show the result of loading this data:

SELECT c1, c2, c3, LENGTH(c2), LENGTH(c3) FROM t1

Given the values of the QUOTES and STRIP options used during the LOAD TABLE command, the following table displays the result of the query, with each result enclosed by angle brackets:

LOAD TABLE Options		Results of SELECT c1, c2, c3, LENGTH(c2), LENGTH(c3) FROM t1
QUOTES	STRIP	c1	c2	c3	length(c2)	length(c3)
ON	RTRIM	<1>	<apple>	<fruit1>	<5>	<6>
		<2>	<banana>	<fruit2>	<6>	<6>
		<3>	< pear >	<fruit3 >	<6>	<8>

ON	OFF	<1>	<apple >	<fruit1 >	<6>	<7>
		<2>	<banana>	<fruit2>	<6>	<6>
		<3>	< pear >	< fruit3 >	<6>	<8>

OFF	RTRIM	<1>	< apple>	< fruit1>	<6>	<7>
		<2>	< “banana”>	< “fruit2”>	<9>	<9>
		<3>	< “ pear ”>	< “ fruit3 ”>	<9>	<11>

OFF	OFF	<1>	< apple >	< fruit1 >	<7>	<8>
		<2>	< “banana” >	< “fruit2”>	<10>	<9>
		<3>	< “ pear ”>	< “ fruit3 ”>	<9>	<11>

Notes on the results:

With QUOTES ON and STRIP RTRIM, both leading space and trailing space for c2 row 1 are trimmed.
With QUOTES ON and STRIP OFF, only the leading space for c2 row 1 is trimmed.
With QUOTES OFF and STRIP RTRIM, only the trailing space for c2 row 1 is trimmed.
With QUOTES OFF and STRIP OFF, neither leading space nor trailing space for c2 row 1 is trimmed.
With QUOTES ON and STRIP RTRIM, both leading space and trailing space within quotes for c2 and c3 row 3 are NOT trimmed.

Example: Load That Skips Specified Fields.

A Windows example:

LOAD TABLE nn
    (l_orderkey,
    l_quantity ASCII(PREFIX 2),
    FILLER(2),
FROM 'C:\\iq\archive\\mill.txt'
BYTE ORDER LOW

Example: Load That Limits the Number of Rows Inserted

In this Windows example, no rows are skipped, and 1,000,000 rows are inserted.

LOAD TABLE lineitem
    (l_shipmode ASCII(15),
    l_quantity ASCII(8),
    FILLER(30))
FROM 'C:\\iq\archive\\mill.txt'
PREVIEW ON
LIMIT 1000000

Example: Load That Includes Tabs and New Lines

The following Windows example sets the column delimiter for the l_orderkey column to tab, and the row delimiter to newline (\x0a) followed by carriage return (\x0d):

LOAD TABLE mm
    (l_orderkey '\x09',
    l_quantity ASCII(4),
    FILLER(6),
    l_shipdate DATE('YYYY/MM/DD'))
FROM 'C:\\iq\\archive\\mill.txt'
ROW DELIMITED BY '\x0a\x0d'

Example: Load That Skips Rows

In this UNIX example, SAP Sybase IQ reads 9,000 rows from the input file, skips the first 5,000, and loads the next 4,000. If there are only 8,000 rows in the input file, only 3,000 rows are loaded.

LOAD TABLE lineitem(
    l_shipmode ASCII(15),
    l_quantity ASCII(8),
FILLER(30))
FROM '/d1/MILL1/tt.t'
LIMIT 4000
SKIP 5000
PREVIEW ON

LOAD TABLE Adds Rows

The LOAD TABLE statement appends the contents of the file to the existing rows of the table.

To empty an existing table, use the TRUNCATE TABLE statement to remove all the rows.