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

Question

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

Answer 1

Hi @Malcolm K

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.

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.