In Snowflake , any one used Merging into table when matched then insert into select statement

Question

In the snowflake ,Merge into when Matched then insert () values () , this is working fine but i have a case where the insert should be from select statements

Merge into when Matched then insert () select * from dummy; - but this is failing with Errors

Answer 1

According to the documentation, the MERGE command allows only:

INSERT [(<col_name> [, ...])] VALUES (<expr> [, ...])

It does not handle the SELECT statement inside an INSERT statement, but it is possible to use a subquery in the USING clause and there you should possibly pass your SELECT and transformations, it could also be a CTE (Common Table Expressions). For example:

merge into target using (
   SELECT k, max (v) as v FROM src group by k) as b --<-- Your subquery
on target.k = b.k
when not matched then insert (k, v) values (b.k, b.v);

or with CTE expression:

with b as (
    SELECT k, max (v) as v FROM src group by k --<-- Your subquery
) 
merge into target using b
on target.k = b.k
when not matched then insert (k, v) values (b.k, b.v); 

Reference: MERGE, CTE