Snowflake merge Query

Question

Hello Team,
I have table 'BATCHTABLE' which is empty table. I want to perform merge on BATCHTABLE.

I want to insert a new record when there is no duplicate record and want to update when there is duplicate record. I verify duplicate record based on batchid and filename are same. when I am running below query it is not inserting any record. Zero insert and zero update. Please advice.

merge into "BATCHTABLE" AS T
using (select BATCHID,FILENAME from "BATCHTABLE") AS S on
T.batchid = S.batchid and T.filename=S.filename
when matched then update set T.filereceiveddate = CURRENT_TIMESTAMP()
when not matched then insert (BATCHID,FILENAME, FILERECEIVEDDATE,FILESEQUENCE)
VALUES('5E322022','testKK003.csv',
CURRENT_TIMESTAMP(),'1');

Answer 1

Hello anonymous userKumarAdari-2857,
Thanks for the question and using MS Q&A platform.
As we understand the ask here is use the merge statement to insert in the data in the same table , please do let us know if its not accurate.
The ask is for Snowflake and I think an snowflake forum should be a better option to ask the question . Anyways since Azure SQL does support Merge , so I thought of going ahead and trying this out .

CREATE Table BATCHTABLE
(
BATCHID varchar(100)
,FILENAME varchar(100)
,FILERECEIVEDDATE datetime
, FILESEQUENCE int
)

merge into BATCHTABLE AS T
using (select BATCHID,FILENAME from BATCHTABLE) AS S on
T.batchid = S.batchid and T.filename=S.filename
when matched then update set T.filereceiveddate = getutcdate()
when not matched then insert (BATCHID,FILENAME, FILERECEIVEDDATE,FILESEQUENCE)
VALUES('5E322022','testKK003.csv',
getutcdate(),'1');

(0 rows affected)
Completion time: 2022-04-14T12:10:39.9930310-07:00

The reason is in your case the source and target table are the same and so the condition

when matched then update set T.filereceiveddate = getutcdate()

is gettting triggered .

For the sake of clarity I update the query to ( i just toggeled the matched with INSERT and unmatched to UPDATE )

merge into BATCHTABLE AS T
using (select BATCHID,FILENAME from BATCHTABLE) AS S on
T.batchid = S.batchid and T.filename=S.filename
when matched then insert (BATCHID,FILENAME, FILERECEIVEDDATE,FILESEQUENCE)
VALUES('5E322022','testKK003.csv',getutcdate(),'1')
when not matched then update set T.filereceiveddate = getutcdate();

Msg 10711, Level 15, State 1, Line 21
An action of type 'INSERT' is not allowed in the 'WHEN MATCHED' clause of a MERGE statement.

I am getting the below error , but please look in the second part of the error "WHEN MATCHED' clause of a MERGE statement." which basically proofs the point which I called out above .

The below query worked for me

merge into BATCHTABLE AS T
using (select BATCHID ='5E322022',FILENAME='testKK003.csv',FILERECEIVEDDATE=getutcdate(),FILESEQUENCE=1) AS S on
T.batchid = S.batchid and T.filename=S.filename
when matched then update set T.filereceiveddate = getutcdate()
when not matched then insert (BATCHID,FILENAME, FILERECEIVEDDATE,FILESEQUENCE) values
(s.BATCHID,s.FILENAME,s.FILERECEIVEDDATE,s.FILESEQUENCE);

(1 row affected)

Completion time: 2022-04-14T13:00:34.5829113-07:00

Please do let me if you have any queries.
Thanks
Himanshu

---

• Please don't forget to click on or upvote button whenever the information provided helps you. Original posters help the community find answers faster by identifying the correct answer. Here is how
• Want a reminder to come back and check responses? Here is how to subscribe to a notification
  • If you are interested in joining the VM program and help shape the future of Q&A: Here is how you can be part of Q&A Volunteer Moderators