TRUNCATE TABLE (Databricks SQL)

Removes all the rows from a table or partition(s). The table must not be a view or an external or temporary table. In order to truncate multiple partitions at once, specify the partitions in partition_spec. If no partition_spec is specified, removes all partitions in the table.

Syntax 

TRUNCATE TABLE table_name [ partition_spec ]

partition_spec
  PARTITION ( { partition_col_name1  = partition_col_val1 } [, ...] )

Parameters

  • table_name

    The name of the table to be created. The name must not include a temporal specification.

  • partition_spec

    Optional comma-separated list of key-value pairs for partitions.

Examples 

-- Create table Student with partition
CREATE TABLE Student (name STRING, rollno INT) PARTITIONED BY (age INT);

SELECT * FROM Student;
+----+------+---+
|name|rollno|age|
+----+------+---+
| ABC|     1| 10|
| DEF|     2| 10|
| XYZ|     3| 12|
+----+------+---+

-- Remove all rows from the table in the specified partition
TRUNCATE TABLE Student partition(age=10);

-- After truncate execution, records belonging to partition age=10 are removed
SELECT * FROM Student;
+----+------+---+
|name|rollno|age|
+----+------+---+
| XYZ|     3| 12|
+----+------+---+

-- Remove all rows from the table from all partitions
TRUNCATE TABLE Student;

SELECT * FROM Student;
+----+------+---+
|name|rollno|age|
+----+------+---+
+----+------+---+