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)
AS
BEGIN
INSERT INTO dbo.account
([name], [accountid], [city])
SELECT
[name], [accountid], [city]
FROM OPENJSON(@json)
WITH (
[name] nvarchar(300) ‘$.name’,
[accountid] nvarchar(300) ‘$.accountid’,
[city] nvarchar(300) ‘$.address1_city’
) AS jsonDataset
END
- 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.: https://stackoverflow.com/questions/40476356/how-do-you-openjson-on-arrays-of-arrays