[resolved] Inserting one-to-many relationship rows into Oracle database

Anonymous — Sat, 16 Nov 2024 11:47:20 GMT

Hi,
I have a situation as follows. My data flow is of the format:
c_id | key | value
---------------
123 | 5 | Harry
123 | 7 | Jones
234 | 5 | John
234 | 6 | S
234 | 7 | Smith
Note that the number of key/value pairs for each c_id is variable and can be anywhere from 1-20 or so
Now, I need to insert this data into an Oracle database consisting of two tables. Table 1 contains the c_id and uses a sequence as its primary key. Table 2 contains the key and value combination with a foreign key reference to Table 1's primary key(the sequence) and it's own sequence as it's primary key. Table 1 and Table 2 have more data associated with them, but it is irrelevant to the question and I am able to invoke the sequences within Talend already.
So, in the above example, I need the Tables to look like this:
Table 1:
PK | c_id
----------
100 | 123
101 | 234

Table 2:
PK | FK | key | value
-------------------------
1 | 100 | 5 | Harry
2 | 100 | 7 | Jones
3 | 101 | 5 | John
4 | 101 | 6 | S
5 | 101 | 7 | Smith

The problem I'm having is with looping in such a way that for each unique c_id, insert the data into Table 1, then for each row with that c_id, add the data to Table 2, and then returning the sequence value being generated (Table 1 PK) for each of the entries into Table 2. The rows will always be sorted based on c_id.
The only way I can see doing this is by getting a unique list of c_id's and creating the entries into Table 1 first. Then, upon completion of that, go back and insert the values into Table 2, selecting the PK from Table1 where the c_id's match, but that seems like an unnecessary inner select.
So, is there a way within Talend that I can group these raw rows by the c_id, then insert one row into Table 1 for each group and then insert a row into Table 2 for each key/value pair within that group?
Thank you

Re: [resolved] Inserting one-to-many relationship rows into Oracle database

Anonymous — Mon, 06 Jan 2014 22:52:56 GMT

My way to do such kind of things is always staging the input data in a table and than separating dimensions and facts (or other dimensions). All possible ways within Talend needs inserts or updates in table1 and commit size == 1 which is extrem slow. If it is not desired to use a staging table and you can read the input source twice without problems then use tAggregateRow to fill table1 and after that read input again and fill table2 via a tMap and a lookup to table1.

Re: [resolved] Inserting one-to-many relationship rows into Oracle database

Anonymous — Tue, 07 Jan 2014 16:03:37 GMT

Thank you.
I decided to go with your second solution as it was much simpler than using a staging table and from my tests is still very fast without needing an inner select.

topic Re: [resolved] Inserting one-to-many relationship rows into Oracle database in Talend Studio

[resolved] Inserting one-to-many relationship rows into Oracle database

Re: [resolved] Inserting one-to-many relationship rows into Oracle database

Re: [resolved] Inserting one-to-many relationship rows into Oracle database