Delete all rows from Teradata table to make it empty

KJazem — Sat, 01 Oct 2022 17:42:46 GMT

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.

Re: Delete all rows from Teradata table to make it empty

Tom — Sat, 01 Oct 2022 18:56:47 GMT

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;

topic Delete all rows from Teradata table to make it empty in SAS Programming

Delete all rows from Teradata table to make it empty

Re: Delete all rows from Teradata table to make it empty