Delete all the data from Database Table

[former_member754957]

Hi Everyone,

I have table with billions of record and I want to delete that record using ABAP .

I tried with statement "Delete from Table_Name" but it is taking too much time and job is getting dumped.

Can anyone suggest any alternate option with that I can delete any no. of record in less time with the help of code?

Thank you,

Aman

[hrmanagerde]

Hi Aman,

you should split it and free the rollback area from time to time with a commit work.

Something like

    DO.
      CLEAR lv_count.
      SELECT * FROM ztable INTO TABLE lt_ztable UP TO 10000 ROWS.
      DESCRIBE TABLE lt_ztable LINES lv_count.
      IF lv_count = 0.
        EXIT.
      ELSE.
        DELETE ztable FROM TABLE lt_ztable.
        COMMIT WORK.
      ENDIF.
    ENDDO.

Good luck!

[matt]

If the table contains so many records, why delete it using ABAP? The easiest way is via SE14 "delete data".

If you've written new functionality for clearing down the table, then DELETE FROM is fine - assuming you never let it get so full again!

Alternatively, you can use the CL_SQL... classes to issue a TRUNCATE TABLE directly to the database. Or an AMDP.

Edit: TRUNCATE is right, not DROP.

[Sandra_Rossi]

TRUNCATE TABLE (delete table contents only) is maybe better suited than DROP TABLE (delete both table contents and definition).

Of course, maybe this syntax is not possible, it depends what database system is connected to the ABAP software (HANA, MSSQL, Oracle, etc.)

[ThorstenHoefer]

Hi amanster,

if you want to delete all rows of the table, you can use function modul

DB_TRUNCATE_TABLE

Best regards

Thorsten

[Florian]

Maybe I'm wrong, but wouldn't

DELETE FROM <table>

be enough?

[matt]

As the OP says, it takes too long and dumps. But...

it can just be run in background of course!

[Florian]

Matthew, thanks mate for adding my thought... jsut the problem with the smartphone..

[rlachemann]

You could use the code below to truncate the table.

The addition 'REUSE STORAGE' avoids shrinking the table to it's initial size.

Please be aware that TRUNCATE is client independent.

              SELECT        * FROM  DD02L
                     WHERE  TABNAME     = '&1'
                     AND    AS4LOCAL    = 'A'.
              ENDSELECT.
              IF DD02L-TABCLASS NE 'TRANSP'.
                WRITE: / '&1 can not be TRUNCATED       .',
                         DD02L-TABCLASS, DD02L-SQLTAB.
              ELSE.
                EXEC SQL.
                  TRUNCATE TABLE &1 REUSE STORAGE
                ENDEXEC.
              ENDIF.

[matt]

Better to use CL_SQL... if possible.