Search This Blog

Saturday, May 25, 2013

Inserting a sequence number/counter into a table’s rows in SQL Loader program

Eg: Create Table T1
(seqCol number(4),
col1 varchar2(15),
col2 varchar2(12))
Create SQL Loader

LOAD DATA INFILE 'data_file_path_name/datafile.dat' APPEND
INTO TABLE T1
FIELDS TERMINATED BY ';' TRAILING NULLCOLS
( seqCol SEQUENCE(1,1) ,
col1 CHAR "LTRIM(RTRIM(:col1))",
col2 CHAR "LTRIM(RTRIM(:col2))" )

The sequence can be as you wish to be started, such as;
SEQUENCE(n,i) – Sequence starts with n(integer) value and increment by i.
SEQUENCE(COUNT,i) – Sequence starts with the number of rows already in the table and increment by i.
SEQUENCE(MAX,i) – Sequence starts with the current maximum value for the column and increment by i.

No comments:

Post a Comment