Upsert issue in ADF

Zhu, Yueli YZ [NC] 235 Reputation points
2023-11-03T14:38:22.0266667+00:00

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

User's image

Azure Storage Accounts
Azure Storage Accounts
Globally unique resources that provide access to data management services and serve as the parent namespace for the services.
2,820 questions
Azure Synapse Analytics
Azure Synapse Analytics
An Azure analytics service that brings together data integration, enterprise data warehousing, and big data analytics. Previously known as Azure SQL Data Warehouse.
4,553 questions
Azure Data Factory
Azure Data Factory
An Azure service for ingesting, preparing, and transforming data at scale.
9,893 questions
{count} votes

Accepted answer
  1. Dronec 171 Reputation points
    2023-11-04T03:03:21.8833333+00:00

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

    It should be like

    INSERT (First_NAME

    ,Last_NAME

    ...


1 additional answer

Sort by: Most helpful
  1. AnnuKumari-MSFT 31,731 Reputation points Microsoft Employee
    2023-11-09T09:09:50.5+00:00

    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