UPSERT to file geodatabase gdb

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

Thank you.