Question

Update and Insert records in a Database

  • 22 August 2019
  • 2 replies
  • 19 views
nedwaterman
Rank
Badge +3

Hi,

 

I have data coming on on a daily basis to a table with no ID field. The data is a volume recorded by a meter every 15 minutes. A user on here called @Takashi kindly helped to put the workspace together, which creates a volume every 15 minutes even if the data is missing (it adds a 0).

 

However, I did this on an INSERT statement and am now finding that new files are coming in that update the previous values. I didn't account for this. I need to alter my workspace to:

find if a record already exists

if the record exists and is not zero don't update the field

if the record doesn't exist add the data.

Any idea how I do this? I'm not sure whether to use a ChangeDetector, FeatureMerger or FeatureJoiner and so am a bit confused!

 

Thanks

Neil

2 replies

redgeographics
Rank
Userlevel 5
Badge +25

What database are you writing to?

If you're simply logging a reading every 15 minutes you shouldn't have to go through change detection (besides... without an ID field, what would you check for?). I think it could be remedied by setting up an ID field. An INSERT should create a new record every time then.

nedwaterman
Rank
Badge +3
Hi there,

 

I'm writing to SQL Server 2016. I'm not sure how setting up an ID field would work - the data doesn't come in with an ID field, which would only be created by the workflow. I'd have to check that the id of the Meter, and the datetime are the same, and if exist, and equal 0, ignore the record, otherwise insert it. Just not sure how to achieve this!

Reply


Community Stats

28,724
Posts
109,517
Replies
36,563
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.