The system integrate data from ArcGis API Services(source) to Microsoft SQL Spatial DB(target).
When the api side have new row of data, i want to check if the data that is already pulled to DB is already existing using let say object_id field. I cannot use upsert at the writer since the flow is that we do not the want any change of pulled data since it will need to go through manual approval for any data changes.
How to insert new row of data only without updating existing data?
Use ChangeDetector
It will say which rows have new Key values, which Keys have been deleted and for which Keys/rows an Attribute value changed, and optionally output a list of the changed values.
It also outputs a Database Operation value of INSERT, UPDATE or DELETE that can be used inside a Writer's DB Operation Parameter that can allow the Writer to simultaneously do 3 separate SQL writes at once, with features entering the writer being sent to the equivalent INSERT, UPDATE or DELETE SQL statement based on the operation value from ChangeDetector.
Thank you! This transformer exactly solves the issues.
Now, I can just select the inserted output from Change Detector. For reference, to use Change Detector transformer, I have to call my target DB as reader and put it as Original input, while the API is the Revised input.
Even though this solves the problem, I encourage any other suggestion in the comment section. Would love to see if there any other alternative solution without having to put the target DB as reader.
Regards.