Skip to main content
Question

Insert and update behavior when read information from a spreadsheet to write in a database

  • May 5, 2020
  • 4 replies
  • 82 views
D

I did a POC where I needed to:

 

1. Read information from an Excel spreadsheet and the insert this fields in certain tables in my postgres database tables.

 

This went fine once. But, when I repeat the process the same "n" numer of rows are inserted again. This is wrong since no change has occured.

 

2. After the first insert I need to update only the rows or fields that changed in that period. As I stated above, whenever I run the flow again and again more rows are inserted (indeed, the same rows are inserted).

 

Is there a way to arrange my model in a fashion that this requirement may be implemented? I mean. Insert once and update incrementally as needed?

 

If so, how? Is there any documentation about it?

 

Regards!

This post is closed to further activity.
It may be an old question, an answered question, an implemented idea, or a notification-only post.
Please check post dates before relying on any information in a question or answer.
For follow-up or related questions, please post a new question or idea.
If there is a genuine update to be made, please contact us and request that the post is reopened.

4 replies

erik_jan
Contributor
Forum|alt.badge.img+26
  • erik_janContributor
  • Contributor
  • May 5, 2020

The transformer you want to have a look at is the ChangeDetector.

This detects changes between your spreadsheet and the table.

Then based on the changes, you can write to the table in INSERT or UPDATE mode, using the attribute fme_db_operation or using two writers (one in INSERT, the other in UPDATE mode).

Hope this helps.

M
Forum|alt.badge.img+2

@diogosanto The tutorial An Introduction to Incremental Database Updates describes the use of fme_dp_operation that @erik_jan mentions

D
  • diogosanto
  • Author
  • May 6, 2020

The transformer you want to have a look at is the ChangeDetector.

This detects changes between your spreadsheet and the table.

Then based on the changes, you can write to the table in INSERT or UPDATE mode, using the attribute fme_db_operation or using two writers (one in INSERT, the other in UPDATE mode).

Hope this helps.

Great! I'm gonna test it!

D
  • diogosanto
  • Author
  • May 6, 2020

@diogosanto The tutorial An Introduction to Incremental Database Updates describes the use of fme_dp_operation that @erik_jan mentions

Excelent! Thank you very much my friend!

Community Stats

32,182
Posts
122,179
Replies
39,856
Members

Latest FME

Terms & ConditionsCookie settingsAccessibility statement
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.