topic Re: Retrieve autoincremented primary key Id for each inserted row and use it in second table in Talend Studio

Retrieve autoincremented primary key Id for each inserted row and use it in second table

Anonymous — Tue, 31 Oct 2017 15:56:20 GMT

Hi ,

Currently I have input source oracle data which I try to save it to table_A which is PostgreSQL database and this table has auto-incremented primary key.

We have below 2 questions:

1] Once we insert this row in Table_A how i can get auto-incremented value and save it Table_B.

2] Is there any feature/components available using which we can compare source data to destination data and decide if it is new data to be inserted or existing data which we need to update...

Thanks,

Re: Retrieve autoincremented primary key Id for each inserted row and use it in second table

TRF — Tue, 31 Oct 2017 16:07:29 GMT

Hi,

1) I'm afraid you have to query the table to get the id for the new rows

2) Check for "Insert or update" (or "Update or insert") option of tPostgresqlOutput component

Re: Retrieve autoincremented primary key Id for each inserted row and use it in second table

Anonymous — Tue, 31 Oct 2017 16:14:49 GMT

Thanks for suggestion but according to what you suggestes for #1 my concern is that i will be able to get those IDs after saving all the records but I would like to get that for each record and I see in talend using tPostgresqlOutput it insert records in bulk like 10k at a time un til then nothing is saved so how we can save one row at a time and get ID in return.

Re: Retrieve autoincremented primary key Id for each inserted row and use it in second table

TRF — Tue, 31 Oct 2017 16:33:24 GMT

You can only retrieve the fields which are in the schema but the autoincrement cannot be part of the flow (using a standard SQL query, you cannot mention serial column in Insert/Update statement).

You may reduce the "Commit every" parameter to 1 then query for the id row by row but it this will dramadically reduce job throughput (not a good idea).

Re: Retrieve autoincremented primary key Id for each inserted row and use it in second table

Anonymous — Tue, 31 Oct 2017 17:46:49 GMT

So If I want to take latest ID after each row insert using below query but how i can map this to the column do we need to use tmap if yes how?

select max(id_store) from store;

Re: Retrieve autoincremented primary key Id for each inserted row and use it in second table

TRF — Tue, 31 Oct 2017 18:52:19 GMT

If the id is declared as a serial (it is, isn't it?), you have a sequence associated to the field.

In your case it should be called "store_id_store_seq" (tablename + column name + seq).

It will be better to ask for the sequence last used value with the following query:

select currval('store_id_store_seq')

This kind of design could be a solution (sorry, I can't try by myself):

tPostgresqlRow will be called for each row (change the lookup model for this flow in the tMap):

It hope this works but once again, not sure as I can't try.

Let us know.

Re: Retrieve autoincremented primary key Id for each inserted row and use it in second table

Anonymous — Thu, 02 Nov 2017 13:53:49 GMT

Hi,

My primary key column is bigserial and its has sequence which you mentioned..but when I do the suggested approach the store_id is always empty in my second table which is referenced table(refer below screenshot) when i want to save it but in the first table ID is saved properly. Please find below screenshots and query use for sequence.

Query: select nextval('rpcs.rp_store_id_store_seq')

screenshots:

Output table:

Re: Retrieve autoincremented primary key Id for each inserted row and use it in second table

Anonymous — Fri, 03 Nov 2017 13:56:42 GMT

Is anybody have any solution or some other idea to implement this?

Re: Retrieve autoincremented primary key Id for each inserted row and use it in second table

Anonymous — Sat, 04 Nov 2017 19:08:31 GMT

Hi All,

Instead of using tpostgresqlrow I used regular tpostgresqlinput with below query and it worked. If somebody would like to use it in future i am just adding screenshot of job for their reference..

select last_value from schemaname.sequencename (here replace your schemaname and sequence name with your)

Re: Retrieve autoincremented primary key Id for each inserted row and use it in second table

TRF — Sat, 04 Nov 2017 20:23:09 GMT

Hi,
You're right tpostgresqlinput is the way to retrieve result from the query.
Don't forget to mark your case as solved.
Kudos could be also appreciated for time left and given directions.

Re: Retrieve autoincremented primary key Id for each inserted row and use it in second table

Anonymous — Tue, 30 Oct 2018 11:49:41 GMT

Hi,

I start in talend and I work on a practical project.
I have a problem with inserting all my rows into my table.
The output of the first tmap returns all the rows, against the output of the second tmap returns single row.
Could you help me please?

5.PNG
6.PNG
7.PNG
8.PNG

Re: Retrieve autoincremented primary key Id for each inserted row and use it in second table

Anonymous — Fri, 02 Aug 2019 13:17:12 GMT

Thanks after read your valuable inputs I have get the PK from table.

Re: Retrieve autoincremented primary key Id for each inserted row and use it in second table

Anonymous — Fri, 02 Aug 2019 13:18:58 GMT

Thanks, It's useful for me.

Ashok Parmar