topic Re: Delete all the data from Database Table in Application Development and Automation Discussions

Delete all the data from Database Table

former_member754957 — Fri, 13 Aug 2021 08:02:24 GMT

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

Re: Delete all the data from Database Table

hrmanagerde — Fri, 13 Aug 2021 08:18:12 GMT

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!

Re: Delete all the data from Database Table

matt — Fri, 13 Aug 2021 08:49:17 GMT

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.

Re: Delete all the data from Database Table

Sandra_Rossi — Fri, 13 Aug 2021 11:38:30 GMT

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.)

Re: Delete all the data from Database Table

ThorstenHoefer — Sun, 15 Aug 2021 17:36:43 GMT

Hi amanster,

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

DB_TRUNCATE_TABLE

Best regards

Thorsten

Re: Delete all the data from Database Table

Florian — Sun, 15 Aug 2021 18:02:51 GMT

Maybe I'm wrong, but wouldn't

DELETE FROM <table>

be enough?

Re: Delete all the data from Database Table

matt — Mon, 16 Aug 2021 07:22:15 GMT

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

it can just be run in background of course!

Re: Delete all the data from Database Table

Florian — Thu, 19 Aug 2021 14:06:56 GMT

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

Re: Delete all the data from Database Table

rlachemann — Thu, 09 Sep 2021 07:16:58 GMT

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.

Re: Delete all the data from Database Table

matt — Thu, 09 Sep 2021 09:57:15 GMT

Better to use CL_SQL... if possible.