Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I am doing update/insert load and I would like to populate different columns in tmap - depend of if Talend is doing insert or update.
for example - populate created timestamp column if it it insert
or populate last modified timestamp column if it is update.
how to differentiate these 2 actions in tmap.
Hi,
I've often found with Talend that its quicker to ascertain as part of your job the INSERTs and UPDATEs before you go to any form of output component (load up the unique key columns into a temporary table and then inner join this against you insert/update table). This way you can split the tDBOutput into either INSERT or UPDATE within the component. Rather than using the components "INSERT or UPDATE" option, for some DB types it performs this as a single row SELECT FROM and then either an INSERT or UPDATE - which is very slow.
I was using single component Update/Insert
and last_modified_timestamp can be null.
I was looking if Talend has global variable for operation because Talend process knows if it's doing Update or Insert and therefore I was looking for something like this
if Insert then
created_timestamp = ....
Else if update then /* or just Else */
last_modified_timestamp = ....
end if;
Hi,
So, if you're doing an INSERT/UPDATE or UPDATE/INSERT, Talend will do a select on what are defined as the key columns, so see if a row exists and then go down the INSERT or UPDATE route accordingly, on a row by row basis. I think for Oracle, its clever enough to ship this logic to the DB so its quick, but for other DB types it has to do it row by row.
Try adjusting the logic to:
Thank you for the update. I understand that I can use oracle triggers as well WHEN inserting vs WHEN updating,
however, I really was looking for solution where I don't need to create extra table just to understand which operation Talend is doing. Talend already know based on query, vs Key column, if it will be update or insert operation and I was looking for to see if that knowledge is also store in any of global variables that can be used in tmap.
Again, thank you for your comment.
Hi,
If you're using Oracle, then I believe that's one of the DB types where Talend correctly renders the INSERT OR UPDATE command, based on you setting the Key columns of your data correctly.
Hi,
If you're using Oracle, then I believe that's one of the DB types where Talend correctly renders the INSERT OR UPDATE command, based on you setting the Key columns of your data correctly.