CODEWITHSUNDEEP
snowflake-cloud-data-platform
danD
danD

Reputation: 716

snowflake delete and reload in the same task

I am writing a task which will delete and reload into a particular table from a Stream.

T2_LOAD_STREAM is a stream defined on a table t2_load which is getting updated by snowpipe. The ask is to delete and reload t2_insert table with the latest value from t2_load.

Below is the attempt I have made so far.

create or replace table t2_load(c1 int,c2 int, d1 date);
create table t2_insert(c1 int,c2 Int,d1 date);

create or replace stream t2_load_stream on table t2_load;

CREATE or replace TASK mytask2
  WAREHOUSE = compute_wh
  SCHEDULE = '5 minute'
WHEN
  SYSTEM$STREAM_HAS_DATA('T2_LOAD_STREAM')
AS
  begin transaction;
  delete from t2_insert;
  INSERT INTO t2_insert select c1,c2,d1 from T2_LOAD_STREAM;
  Commit;

And it does not work. The task get completed but only process begin transaction. How can I process both delete and insert in same task

Upvotes: 0

Views: 864

Answers (2)

H Roy
H Roy

Reputation: 635

Task only support a single SQL statement and if you have multiple statements, use a stored procedure and wrap all your SQLs including any transaction logic and call that stored procedure and it will work fine.

Let me know if this solve your problem.

Upvotes: 1

akshindesnowflake
akshindesnowflake

Reputation: 601

My Observation here is multiple sql statements used for data operarions such as delete and isnert into your transaction unit is not supported in task sql statements portion; why not wrap these sql statments into procedure; obvious it should be javascript based stored procedure and call this stored procedure in sql portion of task

Upvotes: 0

Related Questions