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.
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 ",
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> |
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.
LOAD TABLE nn (l_orderkey, l_quantity ASCII(PREFIX 2), FILLER(2), FROM 'C:\\iq\archive\\mill.txt' BYTE ORDER LOW
LOAD TABLE lineitem (l_shipmode ASCII(15), l_quantity ASCII(8), FILLER(30)) FROM 'C:\\iq\archive\\mill.txt' PREVIEW ON LIMIT 1000000
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'
LOAD TABLE lineitem( l_shipmode ASCII(15), l_quantity ASCII(8), FILLER(30)) FROM '/d1/MILL1/tt.t' LIMIT 4000 SKIP 5000 PREVIEW ON