Teradata TRUNCATE TABLE

In most of the popular RDBMS like Oracle, SQL Server, MySQL, etc. truncate table command is used to delete all the records from an existing table.

The TRUNCATE TABLE and DELETE TABLE both are used in those databases. The main difference between TRUNCATE TABLE and the DELETE TABLE is the maintenance of the transactional log. In the case of the DELETE statement transactional log is maintained while deleting each and every row, however, in the case of the TRUNCATE TABLE command transactional log is not maintained. Hence, the TRUNCATE TABLE command works faster than the DELETE TABLE statement.

Unfortunately, in Teradata, there is no concept of TRUNCATE TABLE. Alternatively, you can use the DELETE ALL statement.

Syntax

The syntax of the Teradata DELETE ALL statement is as follows.

DELETE database_name.table_name ALL;

Example

For the example, we will use the following student_details table.

Table: student_details

select * from tutorialsbook.student_details;

 *** Query completed. 2 rows found. 4 columns returned.
 *** Total elapsed time was 1 second.

    roll_no first_name                last_name                        DOB
----------- ------------------------- ------------------------- ----------
          1 Sagar                     Sharma                    1990-12-04
          2 Shankar                   Kumar                     1995-06-05

The following statement deletes all the records from the student_details table.

DELETE tutorialsbook.student_details ALL;

Output:

DELETE tutorialsbook.student_details ALL;

*** Delete completed. 2 rows removed.
*** Total elapsed time was 1 second.

You can verify the records of the table using the SELECT statement.

select * from tutorialsbook.student_details;

*** Query completed. No rows found.
*** Total elapsed time was 1 second.