DML Commands - General
Removes all rows from a table but leaves the table intact (including all privileges and constraints on the table). Also deletes the load metadata for the table, which allows the same files to be loaded into the table again after the command completes.
Note that this is different from DROP TABLE, which removes the table from the system but retains a version of the table (along with its load history) so that they can be recovered.
- See also:
TRUNCATE [ TABLE ] [ IF EXISTS ] <name>
Specifies the identifier for the table to truncate. If the identifier contains spaces or special characters, the entire string must be enclosed in double quotes. Identifiers enclosed in double quotes are also case-sensitive (e.g.
If the table identifier is not fully-qualified (in the form of
schema_name.table_name), the command looks for the table in the current schema for the session.
Both DELETE and TRUNCATE TABLE maintain deleted data for recovery purposes (i.e using Time Travel) for the data retention period. However, when a table is truncated, the load metadata cannot be recovered.
TABLEkeyword is optional if the table name is fully qualified or a database and schema are currently in use for the session.
-- create a basic table CREATE OR REPLACE TABLE temp (i number); -- populate it with some rows INSERT INTO temp SELECT seq8() FROM table(generator(rowcount=>20)) v; -- verify that the rows exist SELECT COUNT (*) FROM temp; ----------+ count(*) | ----------+ 20 | ----------+ -- truncate the table TRUNCATE TABLE IF EXISTS temp; -- verify that the table is now empty SELECT COUNT (*) FROM temp; ----------+ count(*) | ----------+ 0 | ----------+