cancel
Showing results for 
Search instead for 
Did you mean: 

update a table from a file based on the condition give in the file

abhishekbiwal
Explorer
0 Kudos

Hello experts,

My requirement, i will get a file which will have a column with D or I. D is for delete and I is for insert.

Based on this file, i need to insert or delete records from a table A.

The file will have same structure as table A with an added column (column function) with I or D in it. and i can't use table comparison.

Mt design till on, take a file, use a query transform, split based in the function column on I and D, so i get two set of data one with insert and other with delete.

i use SQL transform to fetch the data from table A, match with the data set with I, and use map operation to insert data in table A.

Similarly i match with the data set for D, and use map operation to delete data from table A.

Not sure this design will or not, and how heavy it will be as this design will try to insert and delete in same data flow

please assist me in a better design

thanks and regards,

Abhishek

Accepted Solutions (0)

Answers (1)

Answers (1)

Former Member
0 Kudos

Hi Abhishek,

You can use the map operation directly after splitting the data, you do not have to use SQL transform to pull the source data, Map operation will take care of it. In map operation-  select delete for normal and discard for all other operaions when you want the records to be deleted and inser for normal and discard for all other operations when you want the records to be insert.

Another way you can do this:

1. Load the table data to a temporary table (can use data tranfer as well).
2. Merge the table data with the file data that are marked for insert (let this dataset be A).
3. Get the data from the file that are marked for deletion (let this dataset be B)
4. Take data only from Data set A that are not in Data set B (A minus B operation). Refer to the link http://scn.sap.com/thread/3408032 -Balamurugan's response.
5. Truncate and then load your target table.

I would suggest you go for the design that you are already working with as the second one is taking a longer time for me.