Skip to content

How to insert JSON from Power Automate to Azure SQL?

In Power Automate, you are handling quite often data in a JSON format which data can come from various sources, Dataverse, APIs etc. Instead of writing JSON data row by row to Azure SQL, you can pass the whole JSON data into the database in one single action. Here’s how you can do it.
  • Go through the JSON data that you need to store and build a stored procedure in SQL database based on it
SQL statement example for creating the procedure:

CREATE PROCEDURE dbo.InsertAccounts

@json NVARCHAR(max)
INSERT INTO dbo.account
([name], [accountid], [city])
[name], [accountid], [city]
[name]      nvarchar(300) ‘$.name’,
[accountid] nvarchar(300) ‘$.accountid’,
[city]          nvarchar(300) ‘$.address1_city’
    ) AS jsonDataset
  • Get JSON data from source X in Power Automate
  • Use Execute Stored Procedure –action, pass the JSON data as a parameter
  • Azure SQL takes care of the rest
Performance comparison with 1000 accounts:

  • Flow without concurrency:  24 mins
  • Flow with concurrency enabled: 6 mins
  • OPENJSON-approach: 1 second

And here’s a good example of how to access to different kinds of elements in your JSON if you have nested arrays etc.:


Leave a Reply

Your email address will not be published. Required fields are marked *