Mapping Columns to Other Columns

If the data file is to have fields in a different order (for example, empno, name, address, hiredate), you can use the INSERT statement to map the columns. First, create the table with the columns in the order in which they are found in the external file.

CREATE EXTERNAL TABLE emp_ext
   (
   f01 INTEGER,
   f02 CHAR(18),
   f03 VARCHAR(40),
   f04 DATE
   )
USING (
   DATAFILES ("DISK:1:/work2/mydir/emp.dat"), 
   REJECTFILE "/work2/mydir/emp.%c.rej"
   );
INSERT INTO employee (empno, name, address, hiredate)
   SELECT * FROM emp_ext;

With this method, the insert columns are mapped to match the field order of the external table.

Another way to reorder columns is to use the SELECT list clause to match the order of the database table.

INSERT INTO employee
   SELECT f02, f04, f03, f01 FROM emp_ext;