Insert Data Into Table With Increment On Key Column

[anmatr]

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.

[SadlerS]

To insert data into a table with an auto-incrementing key column in Talend, you can follow these steps:

Create a sequence in your PostgreSQL database that will generate the auto-incrementing key values for your table. You can do this using the following SQL command:

CREATE SEQUENCE your_sequence_name START 1 INCREMENT 1;

In your Talend job, use a tDBRow component to execute the following SQL command:

SELECT nextval('your_sequence_name');

This will retrieve the next value from the sequence and store it in a global variable that can be accessed by other components in the job.

In your mapping, map the input fields to the output fields as usual, but also include a new column for the auto-incrementing key values. Use the global variable from step 2 to generate a new key value for each row using the following formula:

((Integer)globalMap.get("your_sequence_name_CURRENT_VALUE")) + 1

This will add 1 to the current value of the sequence, which will generate a new key value for each row.

Finally, use a tDBOutput component to insert the mapped data into your PostgreSQL table. Set the "Action on table" property to "Insert" and map the input fields to the corresponding columns in the table. For the key column, map the new column that you created in step 3.

This should allow you to insert data into your table with an auto-incrementing key column using Talend.

[anmatr]

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.

[anmatr]

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.