Delete all rows from Teradata table to make it empty

[KJazem]

Hi.

I have a table on Teradata, and I want, from SAS, to delete all records from the table until it's empty. I know I can drop the table, but is there a way to delete all the rows but keep it as an empty table? 

proc sql;
    delete from TD.MY_TABLE;
quit;

Should something like this work? It hangs for me. I had full access to write/update/delete. 

[Tom · Accepted solution]

SAS does not have a concept of "delete all rows" (also called truncate in other SQL implementations) so it is deleting each observation individually. That might take a long time on a large Teradata table.

Try using pass thru to Teradata so you can use Teradata syntax instead of PROC SQL syntax.

https://www.teradatapoint.com/teradata/teradata-truncate-table.htm

So if you already have a libref connected to teratadata, let's call it TD, then the SAS code would be:

proc sql ;
connect using td;
execute by td
(delete my_table all
);
quit;