snowflake incorporate insert and update to a table

Question

I have three snowflake tables(TEST1, TEST2, CONTROL) as below.

TEST1

create OR REPLACE table TEST1 (
  id varchar(100), 
  name varchar(100),
  address VARCHAR(64)
);    

INSERT INTO TEST1 values (100, 'ABC', null);
INSERT INTO TEST1 values (200, 'XYZ', null);
INSERT INTO TEST1 values (300, 'VBN', null);

TEST2

create OR REPLACE table TEST2 (
  id varchar(100), 
  name varchar(100),
  address VARCHAR(64)
);    

INSERT INTO TEST2 values (100, 'ABC', null);
INSERT INTO TEST2 values (200, 'FDS', null);

CONTROL

create OR REPLACE table CONTROL (
  KEY_COLUMNS VARCHAR,
  TABLE_NAME_SOURCE VARCHAR,
  TABLE_NAME_TARGET VARCHAR
);    

INSERT INTO CONTROL values ('id,name,address', 'TEST2','TEST1');

1. I want to incorporate insert and update to 'TEST2' based on 'TEST1' table.If records does not exist in TEST2, then the stored procedure should insert rows into TEST2 from TEST1

2. If there is any update to TEST1, it should be captured in TEST2.

My stored procedure looks like below. I have declared variables which come from control table. My script works completely fine for insert.

For update, it should update all columns specified in KEY_COLUMNS from TEST1 to TEST2. I can hardcode and update (highlighted in bold), but instead I want to use KEY_COLUMN column from control table as I did for insert something like (WHEN MATCHED THEN update set target_columns = source_columns).

CREATE OR REPLACE PROCEDURE SP() 
    RETURNS string
    LANGUAGE SQL
    AS
    $$  
    DECLARE
           source_tbl STRING := (select TABLE_NAME_SOURCE from CONTROL);
           source_columns STRING;
           target_columns STRING;
           query1 STRING;
     BEGIN
          SELECT KEY_COLUMNS INTO :source_columns FROM CONTROL WHERE TABLE_NAME_SOURCE = :source_tbl;
          SELECT KEY_COLUMNS INTO :target_columns FROM CONTROL WHERE TABLE_NAME_SOURCE = :source_tbl;
          QUERY1 := 'MERGE INTO TEST2 AS A 
                    USING (
                      select '|| :source_columns ||' from TEST1) AS B 
                    ON A.ID=B.ID 
                    WHEN NOT MATCHED THEN INSERT ('|| :target_columns ||') 
                    values ('|| :source_columns ||')
                    **WHEN MATCHED THEN update set A.ID=B.ID, A.name=B.name, A.address=B.address**;';
           EXECUTE IMMEDIATE :QUERY1;

           RETURN :QUERY1;
      END;
      $$;
      
call SP();

Expected TEST2 Output

Answer 1

You want a new statement.

SELECT listagg(' A.'||s.value||'=B.'||s.value, ',') FROM CONTROL, TABLE(SPLIT_TO_TABLE(KEY_COLUMNS, ',')) s WHERE TABLE_NAME_SOURCE = 'TEST2';

which gives:

LISTAGG(' A.'||S.VALUE||'=B.'||S.VALUE, ',')
A.id=B.id, A.name=B.name, A.address=B.address

Thus you SP becomes:

CREATE OR REPLACE PROCEDURE SP() 
    RETURNS string
    LANGUAGE SQL
    AS
    $$  
    DECLARE
           source_tbl STRING := (select TABLE_NAME_SOURCE from CONTROL);
           source_columns STRING;
           target_columns STRING;
           update_sets STRING;
           query1 STRING;
     BEGIN
          SELECT KEY_COLUMNS INTO :source_columns FROM CONTROL WHERE TABLE_NAME_SOURCE = :source_tbl;
          SELECT KEY_COLUMNS INTO :target_columns FROM CONTROL WHERE TABLE_NAME_SOURCE = :source_tbl;
          SELECT listagg(' A.'||s.value||'=B.'||s.value, ',') INTO :update_sets FROM CONTROL, TABLE(SPLIT_TO_TABLE(KEY_COLUMNS, ',')) s WHERE TABLE_NAME_SOURCE = :source_tbl;;
          
          QUERY1 := 'MERGE INTO TEST2 AS A 
                    USING (
                      select '|| :source_columns ||' from TEST1) AS B 
                    ON A.ID=B.ID 
                    WHEN NOT MATCHED THEN INSERT ('|| :target_columns ||') 
                    values ('|| :source_columns ||')
                    WHEN MATCHED THEN update set '|| :update_sets ||';';
           EXECUTE IMMEDIATE :QUERY1;

           RETURN :QUERY1;
      END;
      $$;

which returns

SP
MERGE INTO TEST2 AS A USING ( select id,name,address from TEST1) AS B ON A.ID=B.ID WHEN NOT MATCHED THEN INSERT (id,name,address) values (id,name,address) WHEN MATCHED THEN update set A.id=B.id, A.name=B.name, A.address=B.address;

Answer 2

You can try to build your update statement merge string using value fetched from below query -

SNOWFLAKE1#COMPUTE_WH@TEST_DB.PUBLIC>select * from control;
+-----------------+-------------------+-------------------+
| KEY_COLUMNS     | TABLE_NAME_SOURCE | TABLE_NAME_TARGET |
|-----------------+-------------------+-------------------|
| id,name,address | TEST2             | TEST1             |
+-----------------+-------------------+-------------------+
1 Row(s) produced. Time Elapsed: 0.156s
SNOWFLAKE1#COMPUTE_WH@TEST_DB.PUBLIC>select listagg(concat('A.',value,'=','B.',value),',') as set_upd from (select * fr
                                     om control, lateral split_to_table(control.key_columns,','));
+---------------------------------------------+
| SET_UPD                                     |
|---------------------------------------------|
| A.id=B.id,A.name=B.name,A.address=B.address |
+---------------------------------------------+
1 Row(s) produced. Time Elapsed: 0.185s