topic Re: [resolved] Updating existing Oracle rows in Talend Studio

[resolved] Updating existing Oracle rows

Anonymous — Sat, 16 Nov 2024 12:05:03 GMT

I am attempting to extract data from multiple instances of a Cache' database and import the resulting
rows into a single Oracle database table. The primary key of the Oracle table is a combination of:
Date, LastName, FirstName, TeamName.
The other values associated with each row are numeric. For example: Talk Time, Hold Time, Wait Time, etc.
The source of information can have multiple records with the same Date, LN, FN, TN.
The goal is to insert records into the Oracle table when no other records already exist with the Date, LN, FN, TN
combination. But when this combination already exists, the goal is to add the Talk Time, Hold Time Wait time
of the new record to the values that already exist in the record already in the database.
I've progressed to the point where I am extracting records from Cybase and can put duplicates into the Oracle tables.
Just not sure about how to structure the updates.
I'm a bit new to this. Any help is very much appreciated.
Brian Dailey
Map Communications, Inc.

Re: [resolved] Updating existing Oracle rows

Anonymous — Thu, 14 Mar 2013 05:39:10 GMT

Hi,

I've progressed to the point where I am extracting records from Cybase and can put duplicates into the Oracle tables.
Just not sure about how to structure the updates.

If I understand you correctly, you are trying to insert a record if the key columns alreads exist, otherwise, update the record. Select the item "Update or Insert" in the list of 'Action on data' and check the option "Key" for the columns on the schema, please see my screenshots.
As a newbie, I encourage you to read the online manual of tOracleOutput.
Best regards
Sabrina

Re: [resolved] Updating existing Oracle rows

Anonymous — Thu, 14 Mar 2013 14:06:54 GMT

Hi Sabrina,
At least as I understand it, this method only updates the numeric fields (Talk Time, Hold Time, etc) to equal the contents of subsequent records matching the key. My goal is to add the value of these fields from subsequent records to the values of the same fields in the records that already exist in the database.
For example:
If there is a record in the database already with the following values:
Date Lastname Firstname Teamname TalkTime Holdtime
2013-03-14 Smith Fred Achievers 10 10
And a new record is with the following data is recieved
2013-03-14 Smith Fred Achievers 40 20
The resulting recording in the database should be:
2013-03-14 Smith Fred Achievers 50 30

I'm looking into using tAggregate now, hopefully this is more of the correct approach.

Re: [resolved] Updating existing Oracle rows

Anonymous — Thu, 14 Mar 2013 20:53:08 GMT

I would try to avoid that ! If your job does not work correctly you have no chance to fix your data!
And yes tAggregateRow is the one you need. If your data amount is to large for the in memory processing than use tAggregateSortedRow, it depends on sorted key fields an avoids memory consumption because it release all summary data sets which are already finished.

Re: [resolved] Updating existing Oracle rows

Anonymous — Thu, 14 Mar 2013 21:31:34 GMT

Appreciate the incite. I've got it working with tAggregateRow. But it shouldn't be a problem to switch it to tAggregateSortedRow.