I created a Logic App that can be exported to a Power Apps custom connector & configured to run batch actions on SQL data directly from a Power App. (You could also set something up to send an HTTP request from other services to the same Logic App, but I’ll focus on Power Apps here.)
So this premium connector parameters can take entire collections or select collection columns & filtered rows as JSON, database name, table name, desired operation (CREATE/READ/UPDATE/DELETE), primary key column name, & READ query string.
Then it can pass all those to a Logic App that can efficiently Create, Read, Update, or Delete things in SQL. And the Logic App formats all those parameters & data into single SQL query actions for all the data sent. So there are no slow loops involved.
Now, if your Power App collection columns exactly match your SQL table columns, then you may be able to use some of the simpler methods like PATCH mentioned in these blogs: https://www.matthewdevaney.com/patch-multiple-records-in-power-apps-10x-faster/
https://dev.to/wyattdave/power-apps-patch-vs-update-collect-1foi
Otherwise, let's continue...
Logic App Code:
https://drive.google.com/file/d/1-IHTNZHyZVzsKi3LRy1VIU6zPvEoZzWd/view?usp=sharing
Power Apps Button Formula:
https://drive.google.com/file/d/1LZcuxpu7G_0DHG3rXuXMVygLH6Qf2o8H/view?usp=sharing
Logic App Overview & Power Apps View
Set-Up Steps
Get the Logic Apps code & Power Apps formula from the links or attachments on this post.
Login to your Azure account for your organization. Navigate to Logic Apps and select +Add.
Create a blank Logic App. Input the Logic App subscription, name, & location information. I prefer to use a Consumption subscription plan to help avoid any multiplexing concerns with user-based licensing.
Choose the HTTP Response template if you need to.
Go to the resource. Go to the edit window. Select the code view. Then copy all the Logic App code from the link or attachment on this post & use / paste it to replace all the standard code in the code view.
Then go back to the Designer view by clicking the Designer button next to Code view. Go to the ConditionalIsRead & expand it. Then click on the CreateCSVTypeData action. In this CSV action you can map the SQL column names & their values from the Power Apps data. Other than the 1st Header & Value row, make sure to add a pipe | before each Header & Value to help form a more unique delimiter that won't be disrupted by commas in the data. Use the expression item()?['InsertColumnName'] for the data values you want to map to each SQL column name in the Headers. Also put single quotes ' ' around any values that are string-based values in SQL.
Save the Logic App. Then go back to the Logic App overview screen by clicking the name of your App in the tree menu links above the Logic Apps Designer label. On that overview menu select the Export to Power Apps option. Complete the required items in the menu that pops up and continue.
Then go to your Power App, go to Data tab > Add data > Find your custom connector & select it.
Now add a button to your Power App and in the OnSelect property paste the Power App formula from the link or attachment on this post. Adjust the Collection1 in the formula to whatever collection & data you want to use. Fill in the rest of the parameters with your SQL & data information. If you at any point want to use this for Read requests, you may want to start with a READ OpCRUD and call on the JSON output like in the default formula. Then run the app & press the button.
Your READ data is likely different from the test data. You will need to go back to the Logic App. Navigate into the Logic App run, copy the entire body of the Response action run with your data, then go back to the Logic App edit menu, go to the Response action, select Use sample payload to generate schema, then paste in the Response action run body with your data.
Then I have not found a good way to refresh the custom connector if the input parameters in the HTTP receiver action or response in the HTTP Response action changes. So you will need to re-export the Logic App to Power Apps, re-open your Power App, & add the new custom connector with the correct Response schema for your data.
And that set-up should now allow you to batch Create, Read, Update, & Delete SQL data from that one premium custom connector. This should help fill any gaps with other methods like using PATCH on the entire collection & table.
Thanks for any feedback!
Logic App code & Power App formula text files are in the attached zip file below.
