How to set primary key in sqlldr ctl file?

Question

here i have below file

load data
infile 'emp.csv'
into table EMPLOYEES
append
fields terminated by ',' 
optionally ENCLOSED BY '"'
TRAILING NULLCOLS
(
TENANTID,
EMPLOYEEID,
ISEMPLOYEEACTIVE,
PASSWORD,
EMPLOYEEMANAGERID,
FIRSTNAME,
LASTNAME,
MIDDLENAME,
EMPLOYEEEMAILID,
EMPLOYEEASSISTANTID ,
CATEGORYID,
EMPLOYEENATIONALITY,
BRANCHID ,
GRADEID ,
PASSWORDRESETTOKEN,
PASSWORDRESETCREATEDATE,
ISBRANCHHEAD ,
DEFAULTCOSTCODE ,
GENDER 
)

in this file EMPLOYEEID is primary key so how to set that column as a primary key... because every time it required unique and auto generated number

and here my emp.csv file

1006,1,0,saasas,1049,LoadData,lol,lol,bhargav121@gmail.com,1049,1125,India,2192,1128,1,1,1,1051,m

score 4 · Accepted Answer · answered May 18 '17 at 08:17

4

In db. create sequence EMPLOYEES_SEQ

In ctlfile

...
TENANTID,
EMPLOYEEID "EMPLOYEES_SEQ.NEXTVAL",
ISEMPLOYEEACTIVE,
...

answered May 18 '17 at 08:17

Arkadiusz Łukasiewicz

6,241
1
11
17

score 0 · Answer 2 · answered Oct 01 '21 at 12:49

We could also maintain a column with datatype as NUMBER and constraint GENERATED BY DEFAULT AS IDENTITY. So that while loading, a unique numeric identifier with incremented value for each row will be inserted defaultly.

Egg:

CREATE TABLE members(
    member_id **NUMBER** **GENERATED BY DEFAULT AS IDENTITY**,
    first_name VARCHAR2(50),
    last_name VARCHAR2(50),
    PRIMARY KEY(member_id)
);

For skipping fields/columns while loading through sql loader, visit here

How to set primary key in sqlldr ctl file?

2 Answers2