topic Re: Update table and insert on the same time in Application Development and Automation Discussions

Update table and insert on the same time

Former Member — Fri, 13 Nov 2009 08:03:20 GMT

HI All ,

I have Z DB table which i need to to update via program and i need to use the following process .

1. Assume the table have already user with his data and the process don't have changes in the user data ->dont do anything

2. If the user have new data -> mark the old entry as not valid and insert new entry

3. if there is new user is coming on the update process insert him to the new table and mark the entry as valid

The DB table have this fields as :

1. mandt  "key
2. uuid     "key
3. user 
4 . user data 
5 . time stemp
6. valid_entry  " yes or no

What is the the best solution to handle this issue ?

Regards

Chris

Re: Update table and insert on the same time

MarcinPciak — Fri, 13 Nov 2009 08:12:51 GMT

I think you have answered the question yourself by describing the process step by step. Simply transfer the logic to the code.


select * ....  where ...  "check existence of data for the user
if sy-subrc ne 0 .   "if data doesn't exists
   if new_user = 'X'.
      "new user is coming, new data requeired
       insert ... "insert data with field valid_entry = 'YES'.  
    else.
      "old user, but new data
      update .... set valid_entry = 'NO'.  "first update old entry to invalid
      insert    .... "then insert new data with valid_entry = 'YES'.
    endif.
endif.

This should suffice

Regards

Marcin

Re: Update table and insert on the same time

Former Member — Fri, 13 Nov 2009 08:24:37 GMT

HI Marcin

Thanks

Do you think that i need to use select single (inside loop ) for every user since i can have table with have 100000 users ?

performance is very important in this case

another thing that i consider is to use for the design 2 DB tables since i can have user with 5 records of data

user1 data1

user1 data2

user1 data3

one for the user and another table with user and his entries (like header and lines ) what do you think ?

Regards

Chris

Edited by: Chris Teb on Nov 13, 2009 9:25 AM

Re: Update table and insert on the same time

SuhaSaha — Fri, 13 Nov 2009 08:30:04 GMT

Hello Chris,

Not needed.

1. Select the records in a single shot using SELECT ... INTO TABLE itab. SORT it by the key fields.

2. LOOP AT required table & use READ TABLE itab ... BINARY SEARCH with the key fields to read the data from the internal table & proceed as suggested by Marcin.

Hope this is clear.

BR,

Suhas

Re: Update table and insert on the same time

MarcinPciak — Fri, 13 Nov 2009 08:32:08 GMT

No, I think as far as performance is concerned, you should avoid select statement within loop. This is because for every single record from internal table with user, there has to be established separate session with DB. This has impact on the performace which is low then.

I suggest to get these data at once with select ... for all entries...into table ... (so you connect with DB only once) and you store all the record locally in internal table. Then simply loop at that table and check each record for existence. That will certainly improve the performance.

Regards

Marcin


another thing that i consider is to use for the design 2 DB tables since i can have user with 5 records of data
like
user1 data1
user1 data2
user1 data3
one for the user and another table with user and his entries (like header and lines ) what do you think ?

This is ok from design perspective, but I think it introduces new table to handle with. If there is no to much data about user to store, there is not point in creating separate table to hold that information. Conversely if data about user is huge (like 10-20 fields) you can have them in some "header" table like you said.

Edited by: Marcin Pciak on Nov 13, 2009 9:36 AM

Re: Update table and insert on the same time

Former Member — Fri, 13 Nov 2009 08:32:25 GMT

Select * from Ztable into wa_ztable where uuid = id.

    if sy-subrc = 0.  ""already existing user.

        if wa_ztable <> user_data.  ""the user data is different from the database entry.

           wa_ztable-valid_entry = 'N'.

           update ztable from wa_table transporting all fields. "making the record as not a valid entry

           update ztable from user_data transporting all fields.  " inserting a new record.

        endif.

     else.

       update ztable from user_data transporting all fields.  " inserting a new record.

     endif.