CODEWITHSUNDEEP
sqlmergesnowflake-cloud-data-platformupsert
Gadam
Gadam

Reputation: 3024

SnowFlake MERGE update/insert all columns

Does snowflake support updating/inserting all columns with a syntax like UPDATE * or INSERT *

 MERGE INTO events 
 USING updates 
      ON events.eventId = updates.eventId
 WHEN MATCHED THEN 
      UPDATE *
 WHEN NOT MATCHED THEN 
       INSERT *

similar to how Databricks does it: https://docs.databricks.com/spark/latest/spark-sql/language-manual/delta-merge-into.html

Or do we have to list out each column and its value ?

I am getting the error when I try the above

syntax error ... unexpected '*'.

and the docs dont help much: https://docs.snowflake.com/en/sql-reference/sql/merge.html

Thanks,

Upvotes: 12

Views: 9132

Answers (2)

Andreas Gutweniger
Andreas Gutweniger

Reputation: 61

I helped myself by generating the required SET and INSERT clauses from the source table (in the example: events) structure.

In Snowflake, you can achieve this by using DESCRIBE followed by SELECT * FROM TABLE(RESULT_SCAN(LAST_QUERY_ID()), see https://docs.snowflake.com/en/sql-reference/functions/result_scan.

Adjust to your own needs:

DESCRIBE TABLE events;
SELECT  'MERGE INTO events\n' ||
        'USING updates ON events.eventId = updates.eventId\n' || 
        'WHEN MATCHED THEN UPDATE SET\n' ||
        LISTAGG('\t' || "name" || '=updates.' || "name", ',\n') || '\n' ||
        'WHEN NOT MATCHED THEN\n' ||
        '\tINSERT (' || LISTAGG("name", ', ') || ')\n' ||
        '\tVALUES (' || LISTAGG('updates.' || "name", ', ') || ');'
        AS sql
FROM TABLE(RESULT_SCAN(LAST_QUERY_ID()));

Note: depending on your SQL editor / IDE, it might insert its own queries in between, so the query id you need is not necessarily the most recent one, but one or several before. For my setting (dbeaver) RESULT_SCAN(LAST_QUERY_ID(-2)) worked. Of course you can also look up the query id using the snowflake query history.

Upvotes: 3

Lukasz Szozda
Lukasz Szozda

Reputation: 175556

The UPDATE SET */INSERT * are SQL language extensions(not defined in SQL Standard).

Snowflake does not support that kind of syntax:

MERGE:

matchedClause ::=
  WHEN MATCHED [ AND <case_predicate> ] 
  THEN { UPDATE SET <col_name> = <expr> [ , <col_name2> = <expr2> ...] 
         | DELETE }[...]

notMatchedClause ::=
  WHEN NOT MATCHED [ AND <case_predicate> ] 
  THEN INSERT [ ( <col_name> [ , ... ] ) ] VALUES ( <expr> [ , ... ] )

You could vote for such feature at: https://community.snowflake.com/s/ideas

There is already an item called: "implicit update and insert support for MERGE INTO"

Upvotes: 11

Related Questions