asked on

SQL Loader, inserting a nextval from a sequence.

I have several table in oracle which I insert data into, and I want to user sql loader, the basic format of the table are:

member_id;
client_id constant 123456;
name;
email;

The member_id is a unique member number which is generated from a sequence which I have created:
CREATE SEQUENCE SEQ_MEMBER_ID_TMP
START WITH 468967645
MAXVALUE 999999999999999999999999999
MINVALUE 84761410
NOCYCLE
CACHE 20
NOORDER;

which was created OK.

I just need a control file to insert a list of email and names, and with inserting the member_id and also updating the sequence so that next time it is run the start with value is different as I'm not sure if this will automatically change.

Thanks,

Manoj

cjjclifford

not sure how to get sqlloader to insert values from a sequence - perhaps use sqlloader to load into a staging area (i.e. a seperate table), and then fire a SQL statement to insert into the destination table the data from the staging area, along with SEQ_MEMBER_ID_TMP.NEXTVAL.

Or, you could look at defining an INSERT trigger on the destination table

create trigger seqinserttrig ON destination BEFORE INSERT for each row
begin
select SEQ_MEMBER_ID_TMP.nextval FROM dual INTO :NEW.member_id;
end;
/

Not sure how fast this would be - probably using a staging table (created in a tablespace that has NOLOGGING) is the better way....

ASKER CERTIFIED SOLUTION

seazodiac

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

cjjclifford

there you go - much simpler!