topic Re: how differentiate Insert vs Update in tmap in Talend Studio

how differentiate Insert vs Update in tmap

sradovan — Fri, 19 Jun 2020 22:49:58 GMT

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.

Re: how differentiate Insert vs Update in tmap

TRF — Sat, 20 Jun 2020 05:26:31 GMT

How do you decide to insert or update when arriving at the database component?
Do you have a single component for both operations?
In this case, is it able to ignore null values (do nothing with them) if any?

Re: how differentiate Insert vs Update in tmap

David_Beaty — Mon, 22 Jun 2020 11:16:00 GMT

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.

Re: how differentiate Insert vs Update in tmap

sradovan — Mon, 22 Jun 2020 16:12:55 GMT

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;

Re: how differentiate Insert vs Update in tmap

David_Beaty — Wed, 24 Jun 2020 08:55:02 GMT

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:

Create a temp table
Load the update keys only to temp table from the source.
Inner join the temp table to the destination table as a tDBInput.
Feed the flow from the above step into a tMap and inner join in the source on the update columns.
Any successful joins need to go to a DB component that UPDATEs.
Any inner join rejects need to go to a DB components that INSERTs.

Re: how differentiate Insert vs Update in tmap

sradovan — Wed, 24 Jun 2020 16:37:38 GMT

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.

Re: how differentiate Insert vs Update in tmap

David_Beaty — Thu, 09 Jul 2020 15:34:06 GMT

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.

Re: how differentiate Insert vs Update in tmap

David_Beaty — Thu, 09 Jul 2020 15:34:06 GMT

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.