Modify DB table from internal table or work area

[raffinkira]

Hi all,

I have two ways to updata a DB table.

Option1:

LOOP gt_01 INTO gs_01.

  gs_02-xxx = gs_01-xxx.

  gs_02-yyy = gs_01-yyy.

  APPEND gs_02 TO gt_02.

ENDLOOP.

UPDATE DB FROM TABLE gt_02.

Option2:

LOOP gt_01 INTO gs_01.

  gs_02-xxx = gs_01-xxx.

  gs_02-yyy = gs_01-yyy.

  UPDATE DB FROM  gs_02.

ENDLOOP.

I prefer Option2, but it needs an extra internal table and in my real problem, this table could be very large.

So does it matter if I write UPDATE in the LOOP(Option2), would it cause some DB related performance issues?

[Former Member]

hi ming,

I think first option is better.

internal table is appended then in one go database table will be updated.

[Former Member]

Yes is better option the First, and why you dont use Field-Symbols?

thanks

[Former Member]

In the real problem, the logic of gs_01 and gs_02 is not that simple.

Besides, the point is not field-symbol but comparing modifying from internal table or work area.

[FredericGirod]

Hi,

did you have read the Tips & Tricks from the trans. SE30 ?

regards

Fred

[RaymondGiuseppi]

Using the array to update database shouldl give better performance, you should generate less communication between application server and database server.

But

• In case of error with an internal table you can get some difficulty to identify which record(s) generated the error.
• The internal table may require too many memory, case where you worked with a subset of database fields, but require the whole set fo fields to update the table.
• Sometimes it is better to build some batch of records to update, to prevent a bottleneck in communication between servers if you actually have a huge volumn of data to update or not enough memory.

Regards,

Raymond

[Former Member]

Thanks, your reply helps.

And as far as I tried, if you insert from an internal table, you will get a dump if the internal table contains some duplicate-key records with the DB table.

However, you won't get a dump if you insert from a work area even if the key of the work area has been already existing in the DB table, you just get a sy-subrc 4 instead.

[VenkatRamesh_V]

Hi Ming.

Try,

insert ztable from table itab accepting duplicate keys.

It will not display error.

Hope it Helpful.

Regards,

Venkat.

[Former Member]

I don't know in which case we should insert duplicate key record, it breaks the rule of the database.

And I think that you can use MODIFY instead of INSERT to avoid dump.(Maybe yet the result we need)