question

KunalDeshmukh-0053 avatar image
0 Votes"
KunalDeshmukh-0053 asked MayankBargali-MSFT edited

Upsert in Azure Logic App to load data into Azure SQL

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!

azure-logic-apps
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

1 Answer

MayankBargali-MSFT avatar image
0 Votes"
MayankBargali-MSFT answered MayankBargali-MSFT edited

@KunalDeshmukh-0053 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?

· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @MayankBargali-MSFT,

Thank you very much for your response!

Actually, my workflow is getting failed at Get rows (v2) [Step 2.1] itself and not at Update row (v2). It completes successfully when there is a matching record in the table but, when the record does not exist in the table, it fails with the error message mentioned in my query.

Also, I cannot see any parameter section (to specify Filter Query) for the action Get rows (v2) other than Row id. Are you suggesting me to use 'Execute a SQL Query (V2)' instead? Because, I can see 'Add Parameter' Section there.

Thanks!
Kunal

0 Votes 0 ·

@KunalDeshmukh-0053 There are two different action Get rows (v2) and Get row (v2) action. I belive you are using the Get row (v2) action where there is only one option to specify the Row id. If you don't specify the row id then it get all the rows. In case if you are specifying the Row id and if it does not match then you should get the same error as you have observed.

In my workflow I am talking about Get rows (v2) action where you can specify the filter query. If the record does not match then it will return no rows and if the record matches then you can execute your next actions.

128297-image.png

1 Vote 1 ·
image.png (25.9 KiB)