Auto Increment

One Star

Auto Increment

How to set auto increment feature in talend?
One Star

Re: Auto Increment

In the expression builder, you use Numeric.sequence("myseq",1,1).
You will also need to a a component before the start of the job (tJava) to reset the sequence. I'm doing this off the top of my head, but I believe it is Numeric.resetSequence("myseq",1).
One Star

Re: Auto Increment

Hello,
Thanks for your reply.Today I tried by adding Numeric.resetSquence() in tJava componet. but still I am getting error like ,"
Duplicate entry '2' for key 'PRIMARY' ".Can you please explain where exactly I need to set this Numeric.resetSquence().
Could you please explain your response in detail.
One Star

Re: Auto Increment

OK. You probably do NOT want to use resetSequence if you are loading into a database (unless you are truncating the table). I use resetSequence if I am auto-incrementing on a file output and expect to rerun the job several times, making a fresh file each time.
What type of DB are you loading into. Some can handle auto-increment on their own (MySQL), while others (PostGres / Oracle) use sequences and triggers, which is what Talend is replicating.
One Star

Re: Auto Increment

I have the same question.
I'm using Oracle. Can You describe in more details - how to set autoincrement?
One Star

Re: Auto Increment

Hi, I attached a picture of the tmap where you would set the id.
<edit> having trouble attaching picture ... to populate the auto-id out field named myId in a tmap, the following expression was entered: Numeric.sequence("seqMyId",1,1) <edit>
So the input is coming from somewhere, and the output is going to the database with an additional field myId, which is being auto incremented.
This is sequencing at it's most basic, but does not have a lot of error control. For instance, what if the table being populated was loaded from multiple sources (some outside of Talend)? You could potentially end up with a duplicate key error, or multiple records with the same myId value. Yikes.
Example 2.
To further refine this, you could grab the max(myId) from the table and use that as the start for the sequence.
tOracleInput "select max(myId) as maxId from table1"
Now, you would bring the output from table1 into your tmap and the sequence function would be: Numeric.sequence("seqMyId", table1.maxId, 1)
This is better, but you have to hope that all other processes loading the table are following a similar methodology. And this is why the auto-increment logic is often set in the database and not in the ETL tool.
Here is a link for how to setup an auto-id field in Oracle:
http://earlruby.org/2009/01/creating-auto-increment-columns-in-oracle/
So unless you know that Talend will now and forever be the only tool populating the table, or that the other tools using the methodology in example 2, it is probably best to set your sequencing in Oracle, bearing in mind that there could be a performance hit in doing so (although that can be mitigated too).
Thanks,
Ben
One Star

Re: Auto Increment

Thanx for answer. It was very helpful.
One Star

Re: Auto Increment

my requirement is ,For the first job run sequence will be like 1,2,3... For the next job run it should start from 4 like 4,5,6.... i.e storing the previous value and increment sequence from that value...