Skip to Content
avatar image
Former Member

Append delete and insert in SAP BPODS

I am loading HANA database using BODS. For this job i need to implement the condition If

New records---- Append.
Existing record --- delete and Insert.

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

3 Answers

  • Best Answer
    Aug 05, 2017 at 10:07 PM

    Hi Jyoti deka,

    You can achieve this in 2 ways. Here is the 1st way

    Create a table (name as PARAM_YEAR_PERIOD) with columns

    1.Year

    2.Period

    Now you will have 3 data flows

    1. Source files to PARAM_YEAR_PERIOD table.

    2. PARAM_YEAR_TABLE to delete records from target for the YEAR and PERIOD records.

    3. Source files to your TARGET Table.

    in 1st dataflow take source file-----> Query-----> PARAM table

    in query, map only YEAR and PERIOD column to schema out and select distinct in query. Make YEAR and PERIOD as primary key columns in the query and set use input keys to YES, auto correct load to YES and allow merge to YES in target table options. Enable delete data before loading in the target.

    Here you will load only distinct YEAR and PERIOD in the PARAM table.

    in the 2nd dataflow

    PARAM_YEAR_PERIOD ----> Query---->Map_operation----> Target table

    in query map YEAR and PERIOD to schema out and in the map operation transform set NORMAL to DELETE, here it will delete the records in the target table if YEAR and PERIOD that coming from PARAM table already exists in the target table

    in the 3rd dataflow you will load Source files to your TARGET Table.

    Now 2nd way...........

    Create a table (name as PARAM_YEAR_PERIOD) with columns

    1.Row_num

    2.Year

    3.Period

    4.Status

    Now you will have 2 data flows

    1. Source files to PARAM_YEAR_PERIOD table.

    2. Source files to your TARGET Table.

    in 1st dataflow take source file-----> Query-----> PARAM table

    in query, map only YEAR and PERIOD column to schema out and create new columns Row_num and map gen_row_num() function, and create column STATUS with datatype varchar(1) and map 'N' to it. and select distinct in query. Make YEAR and PERIOD as primary key columns in the query and set use input keys to YES, auto correct load to YES and allow merge to YES in target table options. Enable delete data before loading in the target.

    Here you will load only distinct YEAR and PERIOD in the PARAM table.

    now you have to take a script and create global variable like $G_ROW_NUM, $G_YEAR, $G_PERIOD, $G_COUNT.

    now write script like below

    $G_ROW_NUM=1;

    $G_COUNT=sql('DataStore','select count(*) from PARAM_YEAR_PERIOD');

    print('Number of records in PARAM table : '||$G_COUNT);

    while($G_COUNT>=1)

    Begin

    $G_YEAR=sql('DataStore','select Year from PARAM_YEAR_PERIOD where row-num={$G_ROW_NUM}');

    $G_PERIOD=sql('DataStore','select Year from PARAM_YEAR_PERIOD where row-num={$G_ROW_NUM}');

    sql('DataStore','delete from TARGET_TABLE where year={$G_YEAR} and Period ={$G_PERIOD}');

    sql('DataStore','update PARAM_YEAR_PERIOD set Status='Y' where row_num={$G_ROW_NUM}');

    $G_COUNT=sql('DataStore','select count(*) from PARAM_YEAR_PERIOD where Status=/'N/'');

    if($G_COUNT>=1)

    begin

    $G_ROW_NUM=$G_ROW_NUM+1;

    end

    else

    begin

    $G_COUNT=0;

    end

    end

    Now data for the YEAR and PERIOD that are coming for the source will be deleted in the target table.

    Now you your 2nd dataflow for Source files to your TARGET Table will run.

    Note :- Please do complete testing and implement accordingly, let us know if you have any doubts in this.

    Thanks,

    Ravi kiran.

    Add comment
    10|10000 characters needed characters exceeded

    • Former Member

      Hi Ravi

      Thank you so much. I was trying on different way. I created the flow as you suggested and got the output..

      Thanks

      Jyoti

  • Aug 04, 2017 at 10:27 AM

    HI Jyoti deka,

    tick Delete data before loading in the target table and load it. All existing records will get deleted and all the records in source will get inserted into target table.

    Add comment
    10|10000 characters needed characters exceeded

  • Aug 05, 2017 at 01:30 AM

    Hi Jyoti,

    Based on your requirement. I would suggest SCD2 be the best option for you.

    SCD2 - You can keep old data( History - Updates) and new data( INSERT - Appends ) as data comes from files daily.

    Add comment
    10|10000 characters needed characters exceeded

    • Former Member

      Hi Nagaraju,

      In my requirement I have to delete all the data for that specific periods if the same periods data come in the new file than append all data from that new files(includes new+old data). And if new periods data comes append it.