snowflake conditionally delete from table

Question

What is the best way to implement following Teradata logic in Snowflake?

check if incoming stage table has rows. If so, truncate target table, if not, skip (and go to "endofcode", a label not in the example, we just need to skip)

SELECT * FROM ${STAGE}.STAGE_TABLE SAMPLE 1;
.IF ACTIVITYCOUNT = 0 THEN .GOTO endofcode
BEGIN TRANSACTION;

DELETE FROM ${DWH}.TARGET_TABLE ALL;

thank you.

edit: would this work / is best way? :

DELETE FROM targettab WHERE EXISTS (SELECT 1 FROM stagetab sample (1 rows))

Marcel Marcel · Accepted Answer · 2020-11-06T07:50:24

Regarding this query: DELETE FROM targettab WHERE EXISTS (SELECT 1 FROM stagetab) Yes, it would work.

But: You said you want to TRUNCATE and not DELETE. In Snowflake there are differences between truncating and deleting a whole table: https://docs.snowflake.com/en/sql-reference/sql/delete.html

If you want to use TRUNCATE, you may consider using a Snowflake stored procedure.

snowflake conditionally delete from table

1 Answers