Five Stars

• Oracle Insert into Two table with primary and foreign key

Team, I have a scenario like below, please help us with the solution for the same. Database : Oracle Tables : EMP, EMP_DEFN I’m reading a input file, then process the records and insert them into EMP table firstly. The EMP table has a column EMP_ID_SEQ, to obtain the value for the same I’m using ORACLE sequence. Now here comes the main part of the requirement after obtaining the EMP_ID_SEQ(PK of EMP table) based on sequence, I need to use the same for insert into EMP_DEFN table, EMP_ID_SEQ(FK for EMP_DEFN table). Please note that the link between EMP & EMP_DEFN is only the PK & FK combination. So here I need to insert one record into EMP then obtain the EMP_ID_SEQ then insert into EMP_DEFN table. I need to do this for each record one by one which I feel is the ideal approach. Please let me know an ideal solution for sequential insert or bulk insert.
  • Data Integration
1 ACCEPTED SOLUTION

Accepted Solutions
Eleven Stars

Re: • Oracle Insert into Two table with primary and foreign key

You can do this with a bit of lateral thinking. Essentially what you will be doing is using a tMap with your main data flow with a lookup that will run for every row. The lookup will be driven by a "Select sequence.nextval from DUAL" query. Your tMap output should output to two output flows where the sequence will be shared. Remember to set the lookup table in the tMap to "Load at each row" in the Lookup Model.

Rilhia Solutions
1 REPLY
Eleven Stars

Re: • Oracle Insert into Two table with primary and foreign key

You can do this with a bit of lateral thinking. Essentially what you will be doing is using a tMap with your main data flow with a lookup that will run for every row. The lookup will be driven by a "Select sequence.nextval from DUAL" query. Your tMap output should output to two output flows where the sequence will be shared. Remember to set the lookup table in the tMap to "Load at each row" in the Lookup Model.

Rilhia Solutions