topic Modify DB table from internal table or work area in Application Development and Automation Discussions

Modify DB table from internal table or work area

raffinkira — Wed, 14 Jan 2015 15:43:50 GMT

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?

Re: Modify DB table from internal table or work area

Former Member — Wed, 14 Jan 2015 15:50:16 GMT

hi ming,

I think first option is better.

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

Re: Modify DB table from internal table or work area

FredericGirod — Wed, 14 Jan 2015 15:54:41 GMT

Hi,

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

regards

Fred

Re: Modify DB table from internal table or work area

RaymondGiuseppi — Wed, 14 Jan 2015 16:02:34 GMT

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

Re: Modify DB table from internal table or work area

Former Member — Wed, 14 Jan 2015 22:44:25 GMT

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

thanks

Re: Modify DB table from internal table or work area

Former Member — Thu, 15 Jan 2015 00:55:26 GMT

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.

Re: Modify DB table from internal table or work area

Former Member — Thu, 15 Jan 2015 00:58:24 GMT

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.

Re: Modify DB table from internal table or work area

VenkatRamesh_V — Thu, 15 Jan 2015 02:54:27 GMT

Hi Ming.

Try,

insert ztable from table itab accepting duplicate keys.

It will not display error.

Hope it Helpful.

Regards,

Venkat.

Re: Modify DB table from internal table or work area

Former Member — Thu, 15 Jan 2015 03:45:00 GMT

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)