From Thursday, July, 9, 3:00 PM Pacific,
our Community site will be in
read-only mode
through Sunday, July 12th.
Thank you for your patience.

• Oracle Insert into Two table with primary and foreign key

Highlighted
Six 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.

Accepted Solutions
Highlighted
Community Manager

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.

View solution in original post


All Replies
Highlighted
Community Manager

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.

View solution in original post

Highlighted
Two Stars

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

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,

2019 GARTNER MAGIC QUADRANT FOR DATA INTEGRATION TOOL

Talend named a Leader.

Get your copy

OPEN STUDIO FOR DATA INTEGRATION

Kickstart your first data integration and ETL projects.

Download now

Best Practices for Using Context Variables with Talend – Part 2

Part 2 of a series on Context Variables

Blog

Best Practices for Using Context Variables with Talend – Part 1

Learn how to do cool things with Context Variables

Blog

Migrate Data from one Database to another with one Job using the Dynamic Schema

Find out how to migrate from one database to another using the Dynamic schema

Blog