Loads that Specify Input Data Format

You can specify a wide range of load options that tell 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. For details of all options, see the LOAD TABLE statement in Reference: Statements and Options.

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 ",

The result of loading this data is displayed by running the following query:

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 above, with each result enclosed by ‘<’ and ‘>’:

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 the non-enclosed field c2 row 1 are trimmed.
With QUOTES ON and STRIP OFF, only the leading space for the non-enclosed field c2 row 1 is trimmed.
With QUOTES OFF and STRIP RTRIM, only the trailing space for the non-enclosed field c2 row 1 is trimmed.
With QUOTES OFF and STRIP OFF, neither leading space nor trailing space for the non-enclosed field c2 row 1 is trimmed.
With QUOTES ON and STRIP RTRIM, both leading space and trailing space within quotes for the enclosed fields c2 and c3 row 3 are NOT trimmed.

Example: Load that Skips Specified Fields.

The following is 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 the following Windows example, no rows are skipped and up to 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, 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, then 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; it does not replace the existing rows in the table, unless you specify the START ROW ID load option.

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