Skip to Content
Technical Articles

HANDLING DELETES DATA IN TARGET TABLE

PURPOSE OF THIS DOCUMENTATION:

This documentation helps us to handle deleted data in target table using MAP OPERATION and TABLE COMPARISION transformations.

  • Following are the steps to proceed things in a particular manner.

 

Below flow is used for Handling delete data in target table.

  • Step 1:

Source Data looks like below:

  • Step 2:Query Transformation(QRY_IND)

We created the DELETE_IND column by mapping ‘N’ as default which is used to identify deleted data

  • Step 3: Table comparison

Table comparison would compare the source and target Table and gives an output containing inserts and updates as Input row type for the MAP_Operation Transformation.

Note: We should enable detect deleted rows option to identify the deleted records in the target table so that we can handle those records in the target table by performing further steps

  • Step 4: MAP OPERATION:(INSERT AND UPDATE):

M_I_U map operation operational types should look like below.

  • Step 5: MAP OPERATION (DELETE NORMAL) :

M_D_N map operation operational types should look like below where you convert the deletes detected in the Table_Comparission as normals through Map_Operation.

 

  • Step 6: QRY_DEL()

Map ‘Y’ to the DELETE_IND column which would show as an indicator as ‘Y’ for deleted records

 

  • Step 7: MAP OPERATION (NORMAL UPDATE)

 

  • Step 8: We use MERGE TRANSFORMATION so that we can merge the two map operational data
  • Step 9: Target table data

I executed the job by deleting 4th & 5th records from a source which would show in the target table with ‘Y’ as delete indicator as below.

 

Conclusion: we can see deleted records with delete indicator ‘Y’. the records with delete indicator in ‘N’ are exist in target.

2 Comments
You must be Logged on to comment or reply to a post.
  • I’m reading this one and I’m not quite sure what it’s use is.  It seems like a lot of work to mark something to deleted.   (Modify a table)

    Is there a good time to use this instead of just coding the modify to the table?  I may have just missed it.  Perhaps it’s just a simple example for when we want to do something more difficult.  Have you used this in a project?  If you have, can you share what the requirement was briefly.  I know mark record as deleted.  But not sure why you did it this way.  I know there are a million ways to do one thing in SAP.

    • I can only agree with you. This approach is unneccesarily complicated. Why do people want to reinvent the wheel? You can achieve exactly the same result with a single Map_Operation and no Merge. Check the SAP Data Services Reference Guide for the purpose and functionality of the mapping tab in the Map_Operation transform.