Can I empty a table?

[ezequiel]

Hi, I need to empty a table; I'm working in Progress 8.3

I know there is a "empty temp-table" in 9.1, but this is not a temp-table.

The database is used for a lot of people, so I can't simply stop it for deleting the table and re-create it.

I'm now running a program that deletes the record one by one,, and it will take at least one day.

Is there a trick for emptying this table, like a I did with the old Foxpro "ZAP"? (when I was younger, so much younger than today...)

Thanks

 

[TomBascom]

There is no "empty table". So:

Put the table in a dedicated database and delete the whole db.

Or upgrade to version 9 and use "proutil truncate area".

Both of these solutions require you to have exclusive access to the database.

To do it efficiently with code you need to "chunk" your record by record deletes. Like so:

define variable i as integer no-undo.

/* Use the TRANSACTION keyword to limit the scope of the
 * transaction to this block.  Use the FOR keyword to bind
 * the scope of the tableName record to this block.  Label
 * the outer_loop block to make it clear that there are two
 * iterating loops.
 */

outer_loop: do FOR tableName while true TRANSACTION:

  /* Process the records that you want to delete in groups
   * of 100 (the precise group size doesn't matter much).
   * Every 100 records the outer_loop iterates (when the
   * counter "i" is divisible by 100).  This causes the
   * enclosing transaction to commit 100 records to the
   * database in a group rather than 1 at a time.  For mass
   * updates and deletes that is more efficient.
   */

  for each tableName exclusive-lock:

    delete tableName.

    i = i + 1.
    if i modulo 100 = 0 then next outer_loop.

  end.

  /* If we drop out of the FOR EACH rather then taking the
   * NEXT branch then we have run out of records to process
   * and we must be all done.
   */

  leave outer_loop.

end.