Upsert in Azure Logic App to load data into Azure SQL

Question

Dear Members,

I am trying to create an Azure Logic App which will receive a JSON document from an HTTP Request. This Logic app will then load this JSON data into Azure SQL DB table. But, instead of simply loading the data into SQL DD, I want to implement UPSERT logic through Logic app which will check if the record already exists in the DB table and if it exists, then it will update the record otherwise, will simply insert new record into the table. This can easily be done by creating a Stored Procedure in the Azure SQL DB and then calling this Stored Procedure from Logic app through SQL Server Action/Connector (Execute Stored procedure (V2)). Unfortunately, we cannot create this Stored procedure as we are not the owner of this DB and hence, we need to implement this UPSERT logic completely in the Logic App with the help of Actions/ Connectors.

For this, I am implementing following steps:

1. HTTP Request to get the JSON file
2. For-each to iterate over JSON Objects
   2.1. Get row (V2) action of SQL Server to check if the current JSON record already exists in the table.
   2.2. Condition to check if result of "Get row (V2)"(step 2.1) is empty and based on this condition, the Logic App will run either Update row (V2) or Insert row (V2).

When the Logic app is triggered, it fails at step 2.1 with error as:
"status": 404,
"message": "Item with id '001' does not exist.\r\nclientRequestId: 9f0d4cae-0bd8-461e-979b-0aa1bb689298",
"error": { "message": "Item with id '001' does not exist." }

Is there any way we can implement this upsert logic into Logic App without using Stored Procedure?

Thanks in Advance!

Answer 1

@Kunal Deshmukh When you are using the Get rows (v2) action please make sure that you are using the Filter Query parameter while query the data.

1. Create HTTP trigger to get input the JSON object.
2. Loop in through the JSON object
3. Call Get rows (v2) action and specify the Filter Query parameter with and condition to check with all your input value so it only return the exact matching rows if it is already present in your SQL database.
4. If the Get rows action returns zero row results then you can use Insert rows (v2) action to insert your input record in your SQL table.
5. else (one or more row results) you need to use Update row (v2) to update the value in your SQL table.

As per the error message it looks like the workflow might be failing with the update rows rather than Get rows as we can see the error Item with id '001' does not exist. The get rows should not return this error. Can you confirm it?