CODEWITHSUNDEEP
mergesnowflake-cloud-data-platforminsert-select
prasad bbd
prasad bbd

Reputation: 11

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

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

Upvotes: 1

Views: 1975

Answers (1)

Michael Golos
Michael Golos

Reputation: 2069

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

Upvotes: 1

Related Questions