COPY Examples
1. Load the lineitem.tbl text file into the lineitem table:
COPY TABLE lineitem (
l_orderkey = 'c0|',
l_partkey = 'c0|',
l_suppkey = 'c0|',
l_linenumber = 'c0|',
l_quantity = 'c0|',
l_extendedprice = 'c0|',
l_discount = 'c0|',
l_tax = 'c0|',
l_returnflag = 'c0|',
l_linestatus = 'c0|',
l_shipdate = 'c0|',
l_commitdate = 'c0|',
l_receiptdate = 'c0|',
l_shipinstruct = 'c0|',
l_shipmode = 'c0|',
l_comment = 'c0nl'
) FROM 'lineitem.tbl';
2. Export data:
COPY product_master(
id_pm = c0tab WITH NULL(']^NULL^['),
name_pm = varchar(0)tab WITH NULL(']^NULL^['),
intro_pm = c0nl WITH NULL(']^NULL^['),
nl= d0nl)
INTO 'product_master.data'
3. Import data and continue when an error occurs. Write invalid records to the log file.
COPY product_master(
id_pm= c0tab WITH NULL(']^NULL^['),
name_pm= varchar(0)tab WITH NULL(']^NULL^['),
intro_pm= c0nl WITH NULL(']^NULL^['),
nl= d0nl)
FROM 'product_master.data'
WITH ON_ERROR = CONTINUE, LOG = 'product_master.invalid'
Executing . . .
E_CO0039 COPY: Error processing row 1. Cannot convert column 'intro_pm' to tuple format.
E_US10D0 13 is not a valid month for a date/time column.
E_CO0039 COPY: Error processing row 3. Cannot convert column 'id_pm' to tuple format.
I_CO002C COPY: 2 rows were written to the Copy Log File 'product_master.invalid'.
E_CO0028 COPY: Warning: Copy completed with 2 warnings. 7 rows successfully copied.
(7 rows)
4. Import data but stop after the first error. Write the allowed error record to the log file.
COPY product_master(
id_pm= c0tab WITH NULL(']^NULL^['),
name_pm= varchar(0)tab WITH NULL(']^NULL^['),
intro_pm= c0nl WITH NULL(']^NULL^['),
nl= d0nl)
FROM 'product_master.data'
WITH LOG='product_master.one_error', ERROR_COUNT = 1
Executing . . .
E_CO0039 COPY: Error processing row 1. Cannot convert column 'intro_pm' to tuple format.
E_US10D0 13 is not a valid month for a date/time column.
I_CO002C COPY: 1 rows were written to the Copy Log File 'product_master.one_error'.
E_CO002A COPY: Copy has been aborted.
5. Copy the contents of table t1 into multiple files of 20 MB, and then load the data.
COPY t1() INTO 't1.out' WITH -filesize=20
COPY t1() FROM 't1.out' WITH -filesize