• Oracle Insert into Two table with primary and foreign key

[rajesh90]

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.

[Anonymous · Accepted solution]

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.

[Anonymous]

Hi Sir,

I have tried to do it, however the sequence is the same for all rows, what is wrong here?

I have attached two images about it.

Thanks & Best regards,

image1.png
image2.png