iAnywhere.com - Deleting all rows from a table

Deleting all rows from a table

You can use the TRUNCATE TABLE statement as a fast method of deleting all the rows in a table. It is faster than a DELETE statement with no conditions, because the delete logs each change, while the transaction log does not record truncate table operations individually.

The table definition for a table emptied with the TRUNCATE TABLE statement remains in the database, along with its indexes and other associated objects, unless you execute a DROP TABLE statement.

You cannot use TRUNCATE TABLE if another table has rows that reference it through a referential integrity constraint. Delete the rows from the foreign table, or truncate the foreign table and then truncate the primary table.

TRUNCATE TABLE syntax

The syntax of truncate table is:

TRUNCATE TABLE table-name

For example, to remove all the data in the sales_order table, type the following:

TRUNCATE TABLE sales_order

A TRUNCATE TABLE statement does not fire triggers defined on the table.