[resolved] Incrementing maximum value id plus one every time
I am a beginner to talend and i have the following situation .... I have to get the maximum id plus one every time that I insert a value in the table, its not a sequence its a check while insert data from one table to another. Which component should i use and how to do it? can anybody help me..?
Wait what, I don't understand what you want to do? You have a table like this id¦name 1¦Jcs 2¦Hi and you want to add "hello" as name id¦name 3¦hello into the table ? If the id is auto incrementing, you don't need to get the max id, just add hello into "name", the DB will find the right ID (that's what a auto incrementing id is used for!) (insert into table (name) values ("hello"))
Why do you need to do this as a check? What are you checking? The reason I ask is that this check may cause you problems and may not be efficient. For example, what happens if you carry out the check and another process does something to the table? If you need to do a check before each insert, that is a query and an insert for every insert. That is a lot more expensive than using a sequence, for example.
If you tell us what the check is for, we may be able to suggest an efficient mechanism for achieving this. At the moment, it sounds like you want to do a query before every insert. If that is the case, why not create a function to query the column for you and call that function in the Advanced tab of the t{database}Output component?
Why do you need to do this as a check? What are you checking? The reason I ask is that this check may cause you problems and may not be efficient. For example, what happens if you carry out the check and another process does something to the table? If you need to do a check before each insert, that is a query and an insert for every insert. That is a lot more expensive than using a sequence, for example. If you tell us what the check is for, we may be able to suggest an efficient mechanism for achieving this. At the moment, it sounds like you want to do a query before every insert. If that is the case, why not create a function to query the column for you and call that function in the Advanced tab of the t{database}Output component?
Thanks rhall_2.0 for replying i will elaborate the scenario.....
I have some data in target table, now i have to run a job which will insert records from another table without truncating the target table (Incremental Load), so what i wanted to do is getting the max ID of target table and incrementing the value for every record which is insert.....so is there any good way to do it......................
Wait what, I don't understand what you want to do? You have a table like this id¦name 1¦Jcs 2¦Hi and you want to add "hello" as name id¦name 3¦hello into the table ? If the id is auto incrementing, you don't need to get the max id, just add hello into "name", the DB will find the right ID (that's what a auto incrementing id is used for!) (insert into table (name) values ("hello"))
This sounds like the sort of problem that a sequence is meant for. I would recommend that approach above any other. But, I take it that can't be done. In which case you could do one of the following..... 1) Write a function that queries the max current id, adds 1 and call it on each insert. Not terribly efficient, but would work 2) If you can guarantee that your process is the only process that will touch this table, you could get the max id first and then add 1 to that for every insert. You could so that using Talend global variables or a context variable. 1 would be safer for situations where your process is not the only one interacting with the table. 2 would be faster if you can guarantee that your process is the only one interacting with the table. However, if you can use a database sequence, do that.
I actually agree with Jcs19, but assumed that an auto-increment primary key was not an option (too much time on an Oracle project). I noticed the mssql tag at the top of your post. If you can, it would make sense to use an auto increment primary key. However if you can't, I would go with the sequence.
Why do you need to do this as a check? What are you checking? The reason I ask is that this check may cause you problems and may not be efficient. For example, what happens if you carry out the check and another process does something to the table? If you need to do a check before each insert, that is a query and an insert for every insert. That is a lot more expensive than using a sequence, for example. If you tell us what the check is for, we may be able to suggest an efficient mechanism for achieving this. At the moment, it sounds like you want to do a query before every insert. If that is the case, why not create a function to query the column for you and call that function in the Advanced tab of the t{database}Output component?
Thanks rhall_2.0 for replying i will elaborate the scenario.....
I have some data in target table, now i have to run a job which will insert records from another table without truncating the target table (Incremental Load), so what i wanted to do is getting the max ID of target table and incrementing the value for every record which is insert.....so is there any good way to do it......................
Maybe you can try with the component SCD. U can search the Tutorial with MysqlSCD.
It can auto increment ID number(surrogate key) for the new record with every insert. and update the value without modifyed the ID (compare the source key)
