Insert/update in DBOutput

CloverDX Platform
1

Hi, I’m a new CloverETL user. We’ve been asked at the office to evaluate a couple ETL solutions and I’m assigned to CloverETL. So far, I’m very happy with it and trying to convert the other evaluators. :wink:

Anyway, I’ve managed to start reading and writing to database tables. What I’ve noticed though is that after inserting to a table, my script fails on the second run due to primary key duplicates. Not doing anything really fancy here; just read data off one table, grab a subset of columns, then dump it into my new table. In the other ETL tools we evaluated, there’s usually an option (usually a checkbox) that signifies that we want to do an update if the primary key exists and insert a new row if it doesn’t, so I also checked if there was such an option. I saw this existing thread, but I think the OP is already able to do updates, so he’s a few steps ahead of me.

Am I looking at this in the wrong way or did I miss something really obvious?

Thanks!

2

Hi,

You are right. The thread you mentioned in your post deals with the UPSERT operation. The idea is to read the source data from one table and join them with data in target table. As you can see in our online documentation (http://doc.cloveretl.com/documentation/UserGuide/index.jsp?topic=/com.cloveretl.gui.docs/docs/dbjoin.html), data are joined and sent to output ports accordingly:

  • matched records are sent to output port 0

  • unmatched records are sent to output port 1.

In DBJoin you need to set up the SQL query with WHERE clausule (the key is represented by question mark). You will also need to set up transformation (you can adjust input records as you wish) – this will drive the data sent to output ports.
For more information see attached sample (you will need to change the design according to your scenario).

3

I based my modifications on the attached example. Working as expected now. Thanks!