I see this new UPSERT functionality...
Where can I find help docs for upserting to file geodatabase gdb?
Thank you,
Tyler
SAFE FME 2022.1
Best answer by evieatsafe
View original
UPSERT to file geodatabase gdb
+3
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.
+4
- Contributor
- 45 replies
-
2 November 2022
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.
Userlevel 3
+17
- Safer
- 586 replies
-
3 November 2022
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.
+4
- Contributor
- 45 replies
-
3 November 2022
@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
Userlevel 2
+12
- Contributor
- 152 replies
-
3 November 2022
You can use fme_db_operation. Set the attribut fme_db_operation to INSERT, UPDATE or DELETE.
+4
- Contributor
- 45 replies
-
14 November 2022
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
Userlevel 1
+15
- Safer
- 217 replies
-
17 November 2022
- Best Answer
@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. :)
+4
- Contributor
- 45 replies
-
28 November 2022
@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.
Reply
Enter your username or e-mail address. We'll send you an e-mail with instructions to reset your password.