1.25 Uses of FILLER in COPY

Uses of FILLER in COPY

Following are the uses of FILLER in the COPY statement.

Ignore input columns from the source data:

When bulk loading data, your source data might contain one or more columns that do not exist in the target table. Or, the source and target tables have matched columns, but you want to omit one or more source columns from the target table.

Transform source data before loading it into the target table:

Using SQL operators or functions, you can combine two or more source columns into one column; you can also split one column into multiple columns—for example, split date strings into day, month, and year components and load these into target table columns.

A COPY statement can specify multiple filler columns, where each filler column is specified by its own FILLER parameter:

filler-id FILLER data-type

where filler-id:

• Identifies a source data column.
• Must not match any target column name.

The FILLER parameter identifies a column of source data that the COPY command can ignore, or use to compute new values that are loaded into the target table.

Following is an example of ignoring input columns:

The input file contains 3 columns.

But we dont need to load th 2nd column. Assigning the 2nd field as FILLER makes the 3rd column as 2nd and loads to Daily wages column.

Input File:

sukul1=> \! cat /home/sukul1/empdet.dat

SUKUL|MAHADIK|23.54

RUTUJA|KALE|234.11

BHANU|SINGH|123.11

sukul1=>

Table Structure:

sukul1=> CREATE TABLE USER_30_DAY_TABLES.EMPTABLE

sukul1-> (FIRSTNAME VARCHAR(20),

sukul1(> DAILYWAGE NUMBER(8,2)

sukul1(> )

sukul1-> ;

sukul1=> COPY USER_30_DAY_TABLES.EMPTABLE

sukul1-> (FIRSTNAME,LASTNAME FILLER VARCHAR(20),DAILYWAGE)

sukul1-> FROM LOCAL '/home/sukul1/empdet.dat'

sukul1-> DELIMITER '|'

sukul1-> ABORT ON ERROR

sukul1-> DIRECT;

 Rows Loaded

-------------

           3

(1 row)

sukul1=> select * from USER_30_DAY_TABLES.EMPTABLE;

 FIRSTNAME | DAILYWAGE

-----------+-----------

 RUTUJA    |    234.11

 BHANU     |    123.11

 SUKUL     |     23.54

(3 rows)

Following is the example of transforming data before loading:

The value for 2nd column (INTIALS) needs to be extracted from the 2nd column in the field.

Input File:

sukul1=> \! cat /home/sukul1/empdet.dat

SUKUL|MAHADIK|23.54

RUTUJA|KALE|234.11

BHANU|SINGH|123.11

sukul1=>

Table Structure:

sm017r=> CREATE TABLE USER_30_DAY_TABLES.EMPTABLE

sm017r-> (FIRSTNAME VARCHAR(20),

sm017r(> INITIALS CHAR(1),

sm017r(> DAILYWAGE NUMBER(8,2)

sm017r(> );

sm017r=> COPY USER_30_DAY_TABLES.EMPTABLE

sm017r-> (FIRSTNAME,

sm017r(> LASTNAME FILLER VARCHAR(20),

sm017r(> INITIALS AS SUBSTR(LASTNAME,1,1),

sm017r(> DAILYWAGE)

sm017r-> FROM LOCAL '/home/sm017r/empdet.dat'

sm017r-> DELIMITER '|'

sm017r-> ABORT ON ERROR

sm017r-> DIRECT;

 Rows Loaded

-------------

           3

(1 row)

sm017r=> select * from USER_30_DAY_TABLES.EMPTABLE;

 FIRSTNAME | INITIALS | DAILYWAGE

-----------+----------+-----------

 BHANU     | S        |    123.11

 RUTUJA    | K        |    234.11

 SUKUL     | M        |     23.54

(3 rows)