topic Re: Insert Data Into Table With Increment On Key Column in Talend Studio

Insert Data Into Table With Increment On Key Column

anmatr — Fri, 15 Nov 2024 21:51:17 GMT

The job loads data from a CSV file, maps it using a lookup and should then store the data into a POSTGRES table. This table uses an auto increment key (sequence). It seems that there is no way to specify a sequence on the tDBOutput component so that on any insert the key gets automatically incremented. This is such a basic feature and I cannot believe this is not implemented in Talend.

There is already a thread/question with deals with this problem and the "solution" apparently works, but it is not properly explained. There is just a screen shot and its not clear what configuration on the mapping and the other components is done.

Solution ?

Attached is my job and data - the problem is that the key ID does not get updated on every row. It is only done once and I don't know how to force it to do the sequence lookup on every row passed through the flow.

Re: Insert Data Into Table With Increment On Key Column

anmatr — Sat, 06 May 2023 09:57:59 GMT

Hi, thank you for trying but I'm afraid this is not very helpful. I've added a concrete example and was hoping its progressed from there rather than have some theoretical "steps" shown. In fact you mention steps but its not clear what is step1, step2, etc.

So, when you mention to create a tDBRow component with the sequence, how can it be assigned to a variable? A tDBRow has as output a QUERY, how can this be used to assign a global variable in the same component? This "step" is far to abstract and theoretical to be able to work out the details.

In the next lines you describe to use this formula. But what is "your_sequence_name_" ? The DB sequence name? How can Talend know about that?

Thanks anyway for trying.

Re: Insert Data Into Table With Increment On Key Column

anmatr — Sat, 06 May 2023 12:36:41 GMT

Found an answer now (RTFM, as usual 😉 )

https://help.talend.com/r/en-US/7.3/use-oracle-sequence-insert/set-output-component

This can be applied to a postgres database as well - see attached screen shot.

There is a "Advanced settings" tab on the tDBOutput component which allows to add additional colums.

Add the column name and a SQL expression (nextval('quotes_seq') which is applied to any insert statement.