Below is the code. The error I get is : syntax error line 4 at position 18 unexpected 'NUMBER'. syntax error line 8 at position 18 unexpected '='. I tried to resolve this using chatGPT but they go in circles by asking me to use INTEGER and then NUMBER :)
CREATE OR REPLACE PROCEDURE merge_source_to_target()
RETURNS VARCHAR
LANGUAGE SQL
EXECUTE AS CALLER -- Enables access to session variables
AS
$$
DECLARE v_merge_query_id STRING;
DECLARE v_updates NUMBER;
DECLARE v_inserts NUMBER;
BEGIN
-- Initialize count variables
LET v_updates = 0;
LET v_inserts = 0;
LET v_merge_query_id = '';
-- Perform the MERGE
MERGE INTO target_ns AS t
USING source_ns AS s
ON t.ID = s.ID
WHEN MATCHED AND t.value != s.value THEN
UPDATE SET t.value = s.value
WHEN NOT MATCHED THEN
INSERT (ID, value) VALUES (s.ID, s.value);
-- Capture the query ID of the MERGE operation
LET v_merge_query_id = LAST_QUERY_ID();
-- Retrieve row counts using SYSTEM$GET_QUERY_STATS
LET v_updates = (
SELECT VALUE::INTEGER
FROM TABLE(FLATTEN(INPUT => PARSE_JSON(SYSTEM$GET_QUERY_STATS(v_merge_query_id))))
WHERE KEY = 'rows_updated'
);
LET v_inserts = (
SELECT VALUE::INTEGER
FROM TABLE(FLATTEN(INPUT => PARSE_JSON(SYSTEM$GET_QUERY_STATS(v_merge_query_id))))
WHERE KEY = 'rows_inserted'
);
-- Return the result
RETURN 'Merge completed: ' || v_updates || ' updates, ' || v_inserts || ' inserts.';
EXCEPTION
WHEN OTHER THEN
RETURN 'Error: ' || SQLERRM;
END;
$$;