Inserting records in the table

[Former Member]

Hi All,

I am working on a report where we are inserting huge numbers of records from an internal table to the ztable, I have been advised that rather than inserting all the records at one time we should divide the recods into small blocks may be let's say 100,000 of each block and try inserting the blocks in the ztable. So I was just wondering if anyone of you can help me with this how can I get this?

my internal table is it_data_table and my ztable is z_data_table.

Thanks,

Rajeev

[mvoros]

Hi,

just loop over your table and insert records into temporary table. If the number of records is higher than your limit just insert all records from temporary table, refresh this table and continue. Do not forget to insert last records after loop from your temporary table.

Cheers

[Former Member]

Hi Rajeev,

The best way to insert the records into the table is loop the internal table and use insert command.after insert command, if sy-subrc = 0, commit work and wait.it will do faster.

eg.

data : wa_it_data_table type it_data_table.

loop at it_data_table into wa_it_data_table.

insert into z_data_table values wa_it_data_table.

if sy-subrc = 0.

commit work and wait.

else.

rollback work.

endif.

clear : wa_it_data_table.

endloop.

[Former Member]

Thanks a lot for your reply....but I was just wondering if i INSERT the record in the table using the work area and looping it thhrough the internal table, don't you think it will have performance issues as I will be hiting the database a lot i.e. after every insertion of a single record.

THanks,

Rajeev

[former_member156446]

>

> my internal table is it_data_table and my ztable is z_data_table. 

loop at it_data_table.
count = count + 1.

itab_temp = it_data_table.

if count EQ 10000.

Insert / modify / update statements..z_data_table using itab_temp.
if  sy-subrc eq 0.
refresh itab_temp[].
endif.
endif.

endloop.

[Former Member]

Hi,

To insert records in your ztable from an internal table in blocks, you can use a counter inside the loop.

check the value of the counter, if it reaches the required block value insert records via a temporary table as done in theabove example code.

Regards,

Sachin

[Former Member]

Hello Rajeev,

Here is your solution.

Create an internal table it_data_temp same as it_data_table.

Now write the following code.

*To restrict data (100000  records).

DATA: l_low  TYPE i VALUE 1,
      l_high TYPE i VALUE 100000 .


DO.
  
APPEND LINES OF it_data_table FROM l_low TO l_high TO it_data_temp.


  IF it_data_temp[] IS NOT INITIAL.
 
 UPDATE z_data_table FROM TABLE it_data_temp.
 
    REFRESH: it_data_temp.

    l_low  = l_high + 1.
    l_high = l_high + 100000.
  ELSE.
    EXIT.
  ENDIF.
ENDDO.

Regards

Arindam

[Former Member]

Hi,

Rather than inserting records from the workarea it is better if you use the inetrnal table for insertion. If youu have say 50025 records than it will hit the database that many times which definitely is a performance issue.If you have an internal table say 'it' and another internal table 'it_temp' with a similiar structure, you can try something like as follows

loop at it.

count = count + 1.

it_temp = it.

append it_temp.

if count = 10000.

insert ztable from table it_temp accepting duplicate keys. "to avoid a dump if there are duplicates count = 0.

refresh it_temp.

endif.

endloop.

if it_temp is not initial.

insert ztable from table it_temp accepting duplicate keys.

endif.

In the above code the loop will run the number of record times but, the insert statement will run only 50025/10000 times (i.e, 5 times). After the completion of the loop we need to add the remaining 25 records (because insert will work last inside the loop when 50000 records are reached), for that we will issue the insert statement after completion of the loop, after checking the it_temp table. So the total of 50025 records would be entered in a batch of 6 inserts rather than that many inserts if using just the workarea.

I hope this does help you in your issue.

Thanks and Regards,

Sachin.