Inserting records from PowerApps to SQL Server (Azure SQL)

[Blais]

Hi,

    I want to insert some data in an SQL Server (Azure SQL) relational database table say: Table1.

In PowerApps, I create a collection, Collection1, based on the same table record type RecordTypeT1.

To do that, I made the following steps:

1) Create a record type based on the table1

Apps/OnStart:

Set(EmptyRecordTypeT1; Defaults(Table1));;

2) Create a collection based on table0 data

ClearCollect(Collection1; table0);;

    Table0 is a database table that is identical to Table1 but with some data’s in.

3) Create a collection of empty records

ClearCollect(CollectionOfEmptyRecordTypeT1;ForAll(Collection1; EmptyRecordTypeT1));;

4) Insert Records in the database with "patch"

Patch(Table1; CollectionOfEmptyRecordTypeT1; Collection1)

    I expect the above patch to insert in batch but it’s not the case. Instead, the patch function open a new connection for every line to insert. It takes one (1) second to insert eight (8) records. This is incredibly slow, inefficient. THIS IS INCREDIBLY SLOW AND INEFFICIENT. I can’t imagine that this is a real code implementation. It’s just unbelievable.

Another way to make the insert is to insert line by line in a ForAll loop:

ForAll(Collection1;

       Patch(Table1; EmptyRecordTypeT1; ThisRecord)

);;

   This is as inefficient as the above method.

   Can someone tell me how I’m I suppose to insert records within a database from PowerApps? If there is no efficient way to insert records into a database from PowerApps, people who said that PowerApps is an option to replace Access Application didn’t know what Access or relational database are. Oracle Apex can be an option to Access but not Power Apps.

PowerApps can’t replace Access and is not even close to replace an Access application. PowerApps can actually query some little dataset from a relational database and can write only a couple of record into a relational database. A couple of records meaning a number of record you can count on your fingers. I hope someone will show me a parameter I miss in the patch function to insert record in batch.

[rbrooks66]

You can use a stored procedure with Power Automate.  Here's a link that explains how to accomplish it:

https://www.mssqltips.com/sqlservertip/6857/call-sql-server-stored-procedure-from-power-apps/

I did not use a collection because I was not trying to allow my users to edit the form.  I used an input form only with several drop downs that I tied to different tables in my SQL table.  Just be aware, connecting to SQL is considered a Premium feature in Power Apps and requires a license.

[Blais]

Thank you for this workaround. SQL Server connector is also a premium option. The weird thing is that PowerApps can't work efficiently with a database. I didn't understand that this product is in so well position in the Low-Code Application Platforms benchmark.

Connecting a UI with a database is an essential functionality. It is like saying we have a good text processor that is really good but didn't allow cut and past.

[rbrooks66]

The pricing policy is unfriendly too.  Unless they are trying to discourage using SQL with Power Apps.  I mean, I get $5 per user if you're just doing one app, but the way it was explained to me was that you can pay for a portal for approximately $2500 per year, but that only allows you to run 2 apps.  I keep thinking I must have misunderstood them but I have a coworker who was on the call and he said he heard it the same way.  It just doesn't sound friendly to smaller organizations.

[Blais]

If I 1) transform the collection in JSON, 2) pass the JSON to PowerAutomate who, 3) call an SQL Server Store Proc whom 4) unwrap the JSON and finally 5) Insert the data in the table, that work in an acceptable time.

But this required to A) create an inserting Store Proc for each table in SQL Server and to B) create a PowerAutomate Flow for each table.

It would be a lot simpler if Microsoft just improve the SQL Server Connector to do those repetitive and useless jobs automatically.

[takolota]

@Blais @rbrooks66 

You can try the SQL batch CRUD template here: https://powerusers.microsoft.com/t5/Power-Automate-Cookbook/SQL-Batch-Create-Read-Update-and-Delete/...