CODEWITHSUNDEEP
sql-loader
swank
swank

Reputation: 81

Skip multiple columns in input file in SQL loader

Input file

SegNo|empId|col3|col4|col5..|col9|flightNum|status
1|10|aa|bb|cc|ee|dd|123|active
2|11|ta||cc||sd|205|active
3|12|ay|bb|oc|me|hd|123|inactive
4|10||||ee|dd|123|active

How to write control file to map only columns "SegNo, empId, flightNum, status" into DB. I cannot use position because few records have empty data in certain columns (here record 2 and 4 have empty data in column3, etc).

Upvotes: 1

Views: 2048

Answers (1)

Gary_W
Gary_W

Reputation: 10360

Define those fields in the file as FILLER. Assuming your table called FLIGHT_DATA contains the columns SegNo, empId, flightNum and status:

LOAD DATA
APPEND
INTO TABLE FLIGHT_DATA
FIELDS TERMINATED BY '|'
TRAILING NULLCOLS
(
 SegNo     DECIMAL EXTERNAL   
,empId     DECIMAL EXTERNAL
,col3      FILLER
,col4      FILLER
,col5      FILLER
,col6      FILLER
,col7      FILLER
,col8      FILLER
,col9      FILLER
,flightNum DECIMAL EXTERNAL
,status    CHAR(50)
)

Note the FILLER fields can be called any name you want, as long as they are unique and do not match a column name in the table. The fields in the file have to be accounted for, but they will not be loaded as long as their names do not match and they are defined as FILLER.

EDIT: See this old post for a procedure that generates a control file for you. Tweak as needed: Procedure to generate a control file

Upvotes: 2

Related Questions