question

MalcolmK-0300 avatar image
0 Votes"
MalcolmK-0300 asked MayankBargali-MSFT answered

Inserting Rows to SQL Server using JSON from Data Operations/Compose

Hey

Does anyone know if it is possible to use the SQL Server Insert Rows with a json object instead of having to fill in all of the individual fields in the logic app designer.

Eg :

I have a table in SQL MKTest1 that has 2 fields LogicAppId and RunDate.

Can I use the Data Operations / Compose shape or a Liquid Map to produce JSON like....

{
"LogicAppdId": "some logic apps id",
"RunDate": "20201-04-01 12:34:56"
}

I need to add data to a table with about 60 fields and don't want to have to manually do each field in LogicApps.

I cant use the FOR JSON function in SQL as this is a SQL Server 2012 instance.

My only other thought was to generate the code (using excel or something else easy) to look like the package in the "Code View" in Logic Apps

Eg :
{
"inputs": ``{
"body": {
"LogicAppId": "@variables('strLogicAppId')",
"RunDate": "@variables('strDate')"
}

Any thoughts on this and/or whether a feature to override the JSON Body would be useful.


Thanks as always for any comments / assistance.

Malcolm

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

Hi @MalcolmK-0300

As Insert row v2 action only supports inserting a single row in the table you need to call this action n no of times as per your n records.
Yes, you can define the JSON object as below having the 60 fields that you need to insert.

 [
    {
       "LogicAppdId":"1",
       "RunDate":"20201-04-01 12:34:56"
    },
    {
       "LogicAppdId":"2",
       "RunDate":"20201-04-01 12:34:56"
    }
 ]

Once you have your JSON object you can need to loop in through each object and call the Insert row as below.
For testing, I have defined 2 values in the Parse JSON Content field and you can update as per your requirement or user input value.
You need to update the schema as per your requirement.

83584-image.png

Sharing Definition for your reference:

  "definition": {
         "$schema": "https://schema.management.azure.com/providers/Microsoft.Logic/schemas/2016-06-01/workflowdefinition.json#",
         "actions": {
             "For_each": {
                 "actions": {
                     "Insert_row_(V2)": {
                         "inputs": {
                             "body": {
                                 "LogicAppdId": "@items('For_each')['LogicAppdId']",
                                 "RunDate": "@items('For_each')['RunDate']"
                             },
                             "host": {
                                 "connection": {
                                     "name": "@parameters('$connections')['sql_1']['connectionId']"
                                 }
                             },
                             "method": "post",
                             "path": "/v2/datasets/@{encodeURIComponent(encodeURIComponent('default'))},@{encodeURIComponent(encodeURIComponent('default'))}/tables/@{encodeURIComponent(encodeURIComponent('[dbo].[MyTable]'))}/items"
                         },
                         "runAfter": {},
                         "type": "ApiConnection"
                     }
                 },
                 "foreach": "@body('Parse_JSON')",
                 "runAfter": {
                     "Parse_JSON": [
                         "Succeeded"
                     ]
                 },
                 "type": "Foreach"
             },
             "Initialize_variable": {
                 "inputs": {
                     "variables": [
                         {
                             "name": "val",
                             "type": "integer",
                             "value": "@dayOfWeek('2021-03-28T00:30:00 +05:30')"
                         }
                     ]
                 },
                 "runAfter": {
                     "For_each": [
                         "Succeeded"
                     ]
                 },
                 "type": "InitializeVariable"
             },
             "Parse_JSON": {
                 "inputs": {
                     "content": [
                         {
                             "LogicAppdId": "1",
                             "RunDate": "20201-04-01 12:34:56"
                         },
                         {
                             "LogicAppdId": "2",
                             "RunDate": "20201-04-01 12:34:56"
                         }
                     ],
                     "schema": {
                         "items": {
                             "properties": {
                                 "LogicAppdId": {
                                     "type": "string"
                                 },
                                 "RunDate": {
                                     "type": "string"
                                 }
                             },
                             "required": [
                                 "LogicAppdId",
                                 "RunDate"
                             ],
                             "type": "object"
                         },
                         "type": "array"
                     }
                 },
                 "runAfter": {},
                 "type": "ParseJson"
             }
         },
         "contentVersion": "1.0.0.0",
         "outputs": {},
         "parameters": {
             "$connections": {
                 "defaultValue": {},
                 "type": "Object"
             }
         }

Hope the above helps. Please let me know if you need any assistance.

Please 'Accept as answer' and ‘Upvote’ if it helped so that it can help others in the community looking for help on similar topics.


image.png (38.0 KiB)
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.