You will likely need to read in your existing SQL data to compare the CSV records to the SQL records, with the goal being to separate the CSV records into two categories - Those that are truly new records, and those that have duplicates already in the SQL database.

1. Once you have your SQL data read in, feed them to an AttributeKeeper, keeping only the Account ID.
2. Feed the features into the Supplier port of a FeatureMerger.
3. Feed your CSV records into the Requestor port. When you run the workspace, the Merged port will contain those that have an existing record in the SQL database, and from the UnmergedRequestor port will be the new records.
4. Feed each of these ports into their own AttributeCreators. In the AttributeCreator from the Merged port, create a new attribute called fme_db_operation with a value of Update. In the AttributeCreator from the Unmerged port, set the same attribute to Insert.
5. Feed both of these AttributeCreators into the FeatureWriter.
6. In your FeatureWriter, set the feature operation to fme_db_operation, table handling to Use Existing, and under Row Selection set the Column to Account ID.



This should insert new records, and upload those already present.

So I was able to see the changes I made in the CSV file reflected in the visual preview:

My FeatureWriter:

However, the changes is not reflected in the actual SQL table. Any idea what did I miss?

No records were read by the SQL executor on top.

To ascertain if that ID included in the existing table or not, you should read the existing table.

and, if that GBA ID there, it should emerge from the merged port (featuremerger).

The match id should be GBAID.

@f.kemminje​ is correct. You will either need to provide the SQLExecutor with a feature to trigger the SQL command or use a SQLCreator to return the records. Or use a SQL reader to read the features, which I believe is the best method here. You should only use the Account ID in the Columns parameter of the FeatureWriter.

@f.kemminje​ , @dustin​ thank you for your advice.

Replaced the SQLExecutor with SQLCreator and now it returns results:

Only used AccountID in FeatureWriter as well:

This is my FeatureMerger:

The visual preview indicates AccountID 0083192 has the correct updated value that I need.

However after running the workspace, the SQL db is still not updated with the value that I need:

Anything else I should try? Thanks.

@fmesafejde​It looks like you are trying to update two records in the SQL database with the same GBAID? That may be why it's not updating. Just based on the screenshot, it looks as though you could use GBLT in addition to GBAID as the Column parameter in the FeatureWriter. Let's try that next. Also, what is being done in the AttributeManager right before the FeatureWriter?

I suspect you may need to also add GBSBL in the Column parameter as well, since it looks like there are even duplicates between GBAID and GBLT.

So ended up need to add GBLT, GBSBL, GBFY together with GBAID to the column parameter in the FeatureWriter and the updates finally works! Thanks @dustin​ 

However insert doesn't work even though i added 2 lines to the CSV file. See below:

The two new records are the circled unjoined under DatabaseJoiner2. AttributeCreator_5 is for update which is working fine. AtributeCreator_6 is for insert which is not picking up the 2 new records from the unjoined. Please advise how to fix. Thanks.

If you feed the Unjoined port into the requestor port of the FeatureMerger, it should pick those up.

Excellent @dustin​  that works like a charm!

Just one last question...Currently my AttrributeCreator set as UPDATE to replace any matching values with the new values:

What should I do if I want to append to existing values instead of replacing them? Thanks.

You could setup a published parameter with a choice.

Then in the AttributeCreator, set the value to the parameter.

Sounds good. Thanks for the guidance ! @dustin​