Truncate table in Teradata

access_time 10 months ago visibility487 comment 0

In SQL Server, we can use TRUNCATE statement to clear all the records in a table and it usually performs better compared with DELETE statements as no transaction log for each individual row deletion.

The syntax looks like the following:

TRUNCATE TABLE { database_name.schema_name.table_name | schema_name.table_name | table_name }  
[;]  

Equivalent in Teradata

Unfortunately Teradata doesn't provide TRUNCATE TABLE statement. The similar statement is:

 DELETE TABLENAME ALL;
warning In previous Teradata version, there is performance differences between DELETE and DELETE ALL; however they are not equivalents and there is no performance differences according to Andrew Livingston from Teradata!

Code snippet

DELETE MYTDDB.MYTABLE ALL;


copyright This page is subject to Site terms.
Like this article?
Share on

Please log in or register to comment.

account_circle Log in person_add Register

Log in with external accounts

Follow Kontext

Get our latest updates on LinkedIn or Twitter.

Want to publish your article on Kontext?

Learn more

More from Kontext

visibility 2089
thumb_up 1
access_time 2 years ago

This code snippet shows how to convert string to date in Teradata.

visibility 1156
thumb_up 0
access_time 7 months ago

This page shows how to trim or remove leading or/and trailing zeros using Teradata SQL.

visibility 7
thumb_up 0
access_time 30 days ago

In Teradata, error 'no more room in database' can be common especially when data is not evenly distributed (i.e. high skewness). Sometimes when this error occurs, it might be just because of one or a few AMPs are full. For example, assuming a database with permanent space allocated as 100GB and ...