How to add sequence numbers when importing data through SQL Loader

1. Implement with triggers and Sequences

Create sequence U. seq_questionno
Start with 0
Max value 999999999999999999999999999
Minvalue 0
Nocycle
Nocache
Noorder;

Create or replace trigger U. tr1
Before insert
On U. T1
Referencing old as old New as new
For each row
Begin
Select c1.nextval
Into: New. C1
From dual;
End;

 

Control file:

Load data
Infile 'G: a.txt'
Badfile 'G: A. bad'
Discardfile 'G: A. DSC'

Into table "T1"
Append
Fields terminated by X '9'
Trailing nullcols
(
C2,
C3,
C4,
C5
-- C1
)

2. directly add a sequence to the Control File

Load data
Infile 'G: a.txt'
Badfile 'G: A. bad'
Discardfile 'G: A. DSC'

Into table "T1"
Append
Fields terminated by X '9'
Trailing nullcols
(
C2,
C3,
C4,
C5,
C1 "seq_questionno.nextval"
)

3. No sequence is required. Use recnum in the control file.

Load data
Infile 'G: a.txt'
Badfile 'G: A. bad'
Discardfile 'G: A. DSC'

Into table "T1"
Append
Fields terminated by X '9'
Trailing nullcols
(
C1 recnum,
C2,
C3,
C4,
C5
)