An operation that inserts rows into a database table if they do not already exist,

 or updates them if they do.is called upsert

a combination of the words “update” and “insert.” In the context of relational databases, 

an upsert is a database operation that will update an existing row if a specified value already exists

 in a table, and insert a new row if the specified value doesn’t already exist.

You can handle this in fme transformers and writers.

Right. I get that. I'm looking for help docs on upsert. An upsert option is not popping out at me in my gdb writer, so I want to read more about how to implement the new functionality. Thx.

Hello @townest​ 

You can find an upsert option in the Feature Operation parameter of a file geodatabase writer feature type in FME 2022.1 and newer.

If you are using an existing workspace built in an older FME version, you may need to update the writer in order to see upsert as a parameter option.

@debbiatsafe​ ,

I'll try updating the writer. Thank you.

After reviewing the documentation and thinking this through a bit I have a follow on question. I am transforming CAD objects to GIS features. Upsert will handle inserts and updates, but doesn't appear to handle deletes. What is the best management practice for detecting deleted CAD objects, then deleting the corresponding GIS feature? Thank you.

Tyler

 Safe FME 2022.1

You can use fme_db_operation. Set the attribut fme_db_operation to INSERT, UPDATE or DELETE.

@tomfriedl​, @debbiatsafe​, fme_db_operation requires an attributed record stating INSERT, UPDATE, or DELETE, correct?

Since I'm transforming CAD to GIS....a deleted CAD object will not show up as a record...the object simply won't show up.

So, I'm still wrapped around the axle with regards to the best management practice for updating a GIS table without completely overwriting it every time. Any pointers would be appreciated.

Tyler

Hi @townest​ the best way use the fme_db_operation attribute and have it automatically populated for you is to use the ChangeDetector transformer. Any features that do not exist in the 'Revised' dataset but do exist in the 'Original' dataset will be added to the DELETE port and given the DELETE as the value for the fme_db_operation attribute. See more about how to use this transformer in the documentation or related articles. :)

Thank you.