Solved

Wipe and Replace with FeatureWriter

F
Badge +1

We are on FME Desktop 2021.2.2

Currently have a FeatureWriter that can insert new records (Account ID with their correspondence amounts) into SQL table from a source CSV file. See below:

featurewriter2For each import, the CSV file could have same Account ID with different amounts. Therefore, we'd need to truncate any existing matching Account IDs rows and replace with new data. Changed Table Handling to 'Truncate Existing' but the amount still doesn't change.

Heard you can change Feature Operation to 'upsert' but don't have that option

FeatureOperationThanks.

icon

Best answer by dustin 2 November 2022, 23:11

View original

11 replies

dustin
Rank
Userlevel 3
Badge +26

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.

F
Badge +1

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

FeatureOperation2My FeatureWriter:

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

f.kemminje
Badge +3

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.

 

dustin
Rank
Userlevel 3
Badge +26

@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
Badge +1

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

Replaced the SQLExecutor with SQLCreator and now it returns results:

featurewriter4 

Only used AccountID in FeatureWriter as well:

featurewriter5This is my FeatureMerger:

FeatureMergerThe 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:

DBAnything else I should try? Thanks.

dustin
Rank
Userlevel 3
Badge +26

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

Replaced the SQLExecutor with SQLCreator and now it returns results:

featurewriter4 

Only used AccountID in FeatureWriter as well:

featurewriter5This is my FeatureMerger:

FeatureMergerThe 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:

DBAnything 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.

F
Badge +1

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:

fmeThe 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.

 

 

dustin
Rank
Userlevel 3
Badge +26

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:

fmeThe 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.

F
Badge +1

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:

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

dustin
Rank
Userlevel 3
Badge +26

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:

AttributeWhat 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.

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

image

F
Badge +1

You could setup a published parameter with a choice.

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

image

Sounds good. Thanks for the guidance ! @dustin​ 

Reply


Community Stats

28,651
Posts
109,270
Replies
36,525
Members
Terms & ConditionsCookie settings
A product of
Safe Software respectfully acknowledges that we live, learn and work on the traditional and unceded territories of the Kwantlen, Katzie, and Semiahmoo First Nations.