Upsert issue in ADF

Question

Hi

I followed the instruction about upsert https://learn.microsoft.com/en-us/azure/stream-analytics/sql-database-upsert. But it show the error around "where". But even though I removed where statement, it still didn't work. Following is the query. Thanks

Answer 1

You don't need to specify the table name in the last Insert.

It should be like

INSERT (First_NAME

,Last_NAME

...

Answer 2

Hi Zhu, Yueli YZ [NC] ,

Thankyou for using Microsoft Q&A platform and thanks for posting your question here.

As per my understanding , you are facing error while trying to use Merge statement .

Could you please try adding the where clause in the select query inside USING function. If it doesn't work , kindly try creating CTE with the select query along with where clause and use that cte to perform the merge operation.

Example:

WITH Reorg AS (SELECT *
        FROM DepartmentReorg 
        WHERE GroupName = 'Executive General and Administration')
MERGE INTO HumanResources.Department Dep
USING Reorg
    ON Dep.DepartmentID = Reorg.DepartmentID
WHEN MATCHED THEN
    UPDATE SET Name = Reorg.Name, GroupName = Reorg.GroupName, ModifiedDate = GetDate()
WHEN NOT MATCHED BY TARGET THEN
    INSERT (Name, GroupName, ModifiedDate) VALUES (Reorg.Name, Reorg.GroupName, GetDate());

For more details, kindly check the below relevant document: https://sqlstudies.com/2013/09/30/the-merge-statement-doesnt-have-a-where-clause/

Hope it helps. Kindly accept the answer by clicking on Accept answer button. Thankyou