[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 10631
  • Last Modified:

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
0
mmatharu
Asked:
mmatharu
  • 2
1 Solution
 
cjjcliffordCommented:
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....
0
 
seazodiacCommented:
in your control file:




load data
into table <table_name>
(
member_id "SEQ_MEMBER_ID_TMP.nextval";
client_id constant 123456;
.....
...
)

0
 
cjjcliffordCommented:
there you go - much simpler!
0

Featured Post

Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now