Try to Update record, Insert if not found

CloverDX Platform
1

Hi, I’m building a graph that reads from a remote database, does a simple transform, and loads the information into my local database. I need Clover to attempt an update of my existing data and, if no rows are found to update, insert a new row. I can currently update properly, but I don’t know how to tell Clover to perform an insert if no rows are updated (the update operation “succeeds” even if no rows are updated, so I can’t use error handling).

I couldn’t find a cookbook recipe for this “upsert.” Is there a way to try the update first and then do the insert?

thanks
desmond

2

Hello Desmond,
you can use dictionary for storing the information about number of processed records:updateInsert.png

3

Hi Desmond,

the idea behind upsert implementation split the data into two/three parts
* records you already have in local database → update them in local database
* records you do not have in local database yet → insert them into local database
* (possibly also records, which have been deleted from the remote database → delete them from your local database).
The record identification is usually done by primary key.

Implementation can be done in several ways. Most convenient way is using DataIntersection. You can get similar results using any of Join operations (ExtHashJoin, MergeJoin or DbJoin) depending on the expected volume of data coming from remote database and local database.

Please find below two sample implementations - one using DataIntersection and one using ExtMergeJoin.

4

Upsert implementation with DBJoin; offers great performance especially when input file size (in number of records) is fraction of the database table size (in number of records).

5

hi,
I don’t see join type = Inner join in DBJoin component, can you attach a .grf please?
Thanks for your help.

6

Hi,

Inner join is the default join type. So if Left outer join is false, which is its default value, the inner join type is automatically used.

Hope it helps.

7

Thank you.

But I still have a problem with update.
When I debug the edge between DBJoin and DBOutputTable (update) and view data, it’s the data of the table (Port 0 joined records)…So it try to update data with data already in table not the data of the .csv file!

It’s maybe a problem with “Field Mapping” property of DBOutputTable that I set like documentation ($CloverField:=DBField)
$COUNTRY_CODE:=“COUNTRY_CODE”;$WORDING:=“WORDING”;…

How to set Field Mapping property to update table with the data of .csv file, not with the data already present in the destination table?

8

I understand the problem…In these screenshots there is no information about transformation.

Thanks to this post:
viewtopic.php?f=4&t=6500&p=9954&hilit=upsert#p9954

I have to transform data in DBJoin to get ouput data as input data.

$out.0.* = $in.0.*;

in transform function.