topic DELETE FROM Snowflake table not working in SAS Programming

DELETE FROM Snowflake table not working

mmawwamm — Wed, 16 Oct 2024 14:38:41 GMT

Question about deleting rows from a Snowflake table, I need to delete the rows but keep the table for future use.

Two method, the first one works, but not the second one. Any idea?

PROC SQL;

CONNECT USING SNOW;

DELETE FROM SNOW.my_table;

QUIT;

PROC SQL;

CONNECT USING SNOW;

EXECUTE (DELETE FROM my_table) BY SNOW;

QUIT;

Thanks,

Re: DELETE FROM Snowflake table not working

ballardw — Wed, 16 Oct 2024 16:29:03 GMT

"Not working" is awful vague.

Are there errors in the log?: Post the code and log in a code box opened with the "</>" to maintain formatting of error messages.

No output? Post any log in a code box.

Unexpected output? Provide input data in the form of data step code pasted into a code box, the actual results and the expected results. Instructions here: https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-data-AKA-generate/ta-p/258712 will show how to turn an existing SAS data set into data step code that can be pasted into a forum code box using the "</>" icon or attached as text to show exactly what you have and that we can test code against.

Not sure about Snowflake.

A data step like this is a common way to keep the structure of a data set in SAS but "empty" it:

data lib.dataset;
   set lib.dataset (obs=0);
run;

The Sql equivalent leaves a warning in the log:

proc sql;
   create table have as
   select * 
   from have (obs=0)
  ;
quit;

I have no idea if the Obs= dataset option will work with Snowflake though.

Re: DELETE FROM Snowflake table not working

Tom — Wed, 16 Oct 2024 16:51:11 GMT

You might have to add the schema name used when defining the libref into the explicit Snowflake code.

EXECUTE (DELETE FROM my_schema.my_table) BY SNOW;

Check the LIBNAME statement that made the SNOW libref to see what schema it is pointing at.

Check with Snowflake documentation for how it handles schema names.

Also check with Snowflake documentation for the best way to "empty" a table. Many databases have special syntax for that. (I seem to remember that Oracle used TRUNCATE statement?).

Note: You can move the BY SNOW to the front if you want.

EXECUTE BY SNOW (....);

That way the code looks more like the code used with the FROM CONNECTION TO xxx syntax used when using explicit passthru code in a query.