How To: Perform a Snowflake MERGE/UPSERT from a flat file staged on S3

To Nha Notes | Nov. 2, 2022, 2:10 p.m.

A merge or upsert operation can be performed by directly referencing the stage file location in the query.

Below is an example:

MERGE INTO foo USING
(SELECT $1 barKey,
 $2 newVal,
 $3 newStatus,
 ...
FROM @my_stage( FILE_FORMAT => 'csv', PATTERN => '.*my_pattern.*')
) bar ON foo.fooKey = bar.barKey
WHEN MATCHED THEN
 UPDATE SET val = bar.newVal, status = bar.newStatusWHEN NOT MATCHED THEN
 INSERT
 (val, status
 ) VALUES
 (bar.newVal, bar.newStatus
 );

It is important to add an alias to individual columns projected from the stage file as well as adding an alias to the staging location in the FROM clause and reference it in the join condition to avoid ambiguity.

Additional Information

Please refer to the following online documentation link to obtain more information about the MERGE command:
https://docs.snowflake.net/manuals/sql-reference/sql/merge.html

URL Name

how-to-perform-a-mergeupsert-from-a-flat-file-staged-on-s3

Title

How To: Perform a MERGE/UPSERT from a flat file staged on S3

References

https://community.snowflake.com/s/article/how-to-perform-a-mergeupsert-from-a-flat-file-staged-on-s3