Skip to Content
avatar image
Former Member

Update/Insert/Delete logic using BODS Transformations

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.

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

2 Answers

  • Oct 19, 2016 at 02:59 PM

    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.

    Add comment
    10|10000 characters needed characters exceeded

    • 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.

  • avatar image
    Former Member
    Nov 09, 2016 at 11:04 AM

    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

    Add comment
    10|10000 characters needed characters exceeded