• Home
  • Forums
  • FME Form
  • Data
  • Insert and update behavior when read information from a spreadsheet to write in a database
Question

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

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!

4 replies

erik_jan
Rank
Userlevel 2
Badge +16

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
Badge +2

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

D

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 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!

Reply


Community Stats

28,697
Posts
109,444
Replies
36,546
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.