Loading from a Fixed-ASCII File

The following SQL statements load data from the emp_exp external table to a fixed-position table (employee):

CREATE EXTERNAL TABLE emp_ext 
   ( name CHAR(18) EXTERNAL CHAR(18), 
     hiredate DATE EXTERNAL CHAR(10), 
     address VARCHAR(40) EXTERNAL CHAR(40), 
     empno INTEGER EXTERNAL CHAR(6) ) 
USING (
   FORMAT 'FIXED',
    DATAFILES ("DISK:1:/work2/mydir/emp.fix")
    );
INSERT INTO employee SELECT * FROM emp_ext;

The enumerated columns use the keyword EXTERNAL to describe the format in which to store the data in the external file.

If the data file has fields that you do not want to insert, you can define dummy fields so that you can skip them. Consider the following scenario:

TABLE company (
   comp_no SERIAL,
   acode DECIMAL(3),
   city CHAR(15),
   state CHAR(2),
   zip_code DECIMAL(5),
   phone CHAR(8),
   dateentered DATE); 

The data file consists of the following two rows:

Sunnyvale++++++CA94086408-789-8075
Tempe++++++++++AZ85253xxx-xxx-xxxx 

The SQL statements to load this file would be as follows:

CREATE EXTERNAL TABLE extcompany 
   ( city CHAR(15) EXTERNAL CHAR(15), 
     state CHAR(2) EXTERNAL CHAR(2), 
     zip DECIMAL(5) EXTERNAL CHAR(5), 
     area DECIMAL(3) EXTERNAL CHAR(3) NULL 'xxx', 
     dummychar CHAR(1) EXTERNAL CHAR(1), 
       { this is the '-' }
     phone CHAR(8) EXTERNAL CHAR(8) NULL 'xxx-xxxx', 
     dummyeol CHAR(1) EXTERNAL CHAR(1) 
       { this is the newline character } 
     )
using (
   FORMAT 'FIXED',
    DATAFILES("DISK:1:/work2/mydir/company.fix") 
    );
INSERT INTO company(city, state, zip_code, acode, phone)
SELECT TRIM(trailing '+' from city), state, zip, area, phone 
FROM extcompany;

In the internal table, the column dateentered is not initialized from the external data file, so you do not enumerate it in the list of columns for the insert table. Columns that you do not enumerate are loaded with the default values defined for the columns on the target table.

You can use the TRIM function to clean up data before you enter it in the table.