Question

UPDATE SQL Server & UpdateDetector

  • 19 March 2019
  • 5 replies
  • 2 views
G
Badge

Hello,

I have inserted an Excel spreadsheet in SQL Server with total rows = 6.357. I want to update the existing table with an updated version of the same spreadsheet (i.e. 2 row values have changed; else remains the same).

 

My workflow is:

Readers: (I) new excel spreadsheet and (ii) connection to the existing table in db. I then use the UpdateDetector transformer and the output is: Unchanged: 6.356 and Inserted: 1 and Deleted: 1.

This one is the one I modified in excel, so it is the right one. My modification however, was to add a prefix in two of the column values of one row.

Thus, I would expect to find the change in the UPDATE. Could you please enlighten me why this happens?

What I wish to achieve is, update the db with all the changes of the spreadsheet in any time. Do you think it is feasible, without having to specify a column value on the where clause?

I want to automate the process of updating the db based on an updated spreadsheet using FME Server. This means, I do not want to be the one that specifies the where clause or how the update will be performed, I was wondering if there is a "smarter" way to apply in the workbench to recognize all the changes that have been made and update accordingly.

 

I have been thinking to use INSERT and always Drop Existing, but this means that people will need to make sure that they do not try to make an update at the same time, which is what I am trying to avoid.

 

Thank you for your time.

Kind regards,

George Floros

5 replies

david_r
Rank
Userlevel 4

How did you configure the UpdateDetector? Do you have a primary key to detect changes by?

G
Badge

How did you configure the UpdateDetector? Do you have a primary key to detect changes by?

hello @david_r,

 

I used an existing column that holds unique IDs for all rows.

david_r
Rank
Userlevel 4

The most common issue with this transformer is this phrase in the help:

Please note that to get correct results, the key attribute must be provided to both "Key Attribute" and "Attribute(s) to match" parameter prompts.

Make sure that your common ID has been specifed both places, otherwise the behavior will not be as expected:

You may also want to consider upgrading to FME 2019 which has a completely revised ChangeDetector that can now also detect updates, but with a much more intuitive interface. I suspect that the performance is also much improved.

 

G
Badge

The most common issue with this transformer is this phrase in the help:

Please note that to get correct results, the key attribute must be provided to both "Key Attribute" and "Attribute(s) to match" parameter prompts.

Make sure that your common ID has been specifed both places, otherwise the behavior will not be as expected:

You may also want to consider upgrading to FME 2019 which has a completely revised ChangeDetector that can now also detect updates, but with a much more intuitive interface. I suspect that the performance is also much improved.

 

I have been using this reference id both as a key attribute and as an attribute to match. Could it be related to how Excel translates insert or update?

david_r
Rank
Userlevel 4

I have been using this reference id both as a key attribute and as an attribute to match. Could it be related to how Excel translates insert or update?

I'm not sure Excel is involved here, but it's difficult to say without knowing your data. My first tip would be to look very carefully at the features entering the UpdateDetector. You could e.g. use breakpoints or to the Inspector. In particular you should look at the updated feature and how it looks when it arrives at the Original and Revised ports.

Reply


Community Stats

28,716
Posts
109,493
Replies
36,561
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.