Update/Insert/Delete logic using BODS Transformations

[Former Member]

Hi All,

This new job what i have created will be running based on last_modified >= last_extracted_dt.

if in case, we have records from source and in it, there are records deleted from source, those should get deleted from target as well (simultaneously upsert logic(Update/Insert) should also be in place). This logic should be on a existing HANA target table.

Can someone please guide me on this.

Below is the flow, i am trying to use but the delete logic is removing all records from target when it didn't find any records in source table.

Thanks,

Abdulrasheed.

[Former Member]

Have changed the strategy little bit in here, where the stage is not truncate load. Stage will be having complete data which most likely be same count of target table count. Now the challenge is. we have four columns in both stage and target as ACTV_ID, ACT_SRC_CD, PART_NBR, PART_KEY_ID. In these, first three are key columns, where as the last PART_KEY_ID is not. When there is a record got updated of PART_NBR in source, i m trying to update the same in Target by keeping ACTV_ID,ACT_SRC_CD in ID list and PART_NBR in compare column list of Table compassion with Map_operation(update -> update). But it is not working. I can understand that PART_KEY_ID is not a key column and even if we keep this in IDs list(Table comparison), it doesn't matter. will it be? Will there be a way to implement a logic where in this scenario, the updated source record can get updated into target table. Please assist

[akhileshkiran]

Hi Abdul,

I thinks from the above image you are using the target table two time right. If you want to insert update and delete the data in the target table based on the source.

You can normally use the Map_Operation directly(with default op options). Enable Detect Deleted rows(s) from the table comparison table.

Regards,

Akhilesh Kiran.

[Former Member]

Hi Kiran,

Thanks for your reply. Here is what I am doing currently.

• First Data flow - Source to Stage data based on last_modified >= last_extracted_dt(stage is truncate load)

• Second Data flow – Stage to Target with Upsert(Insert/Update with Auto correct load option on target table) logic

• Third flow – Stage to Target again but with Table comparison -> Map operation (delete -> delete)

With this first two flows works fine but the last flow is deleting the expected record in target along with all records as well.

I have 6000 plus records in stage and 32 million in target where only 1 record of an ACTV_ID, i have deleted from stage for testing and i am expecting all the records of target should remain as is by deleting only 1 record from target. Target is having total three records of this ACTV_ID. Hope i am clear.

Please assist.