SQL Batch Create Read Update and Delete 08-12-2022 21:22 PM - last edited 04-26-2024 11:40 AM

[takolota]

Generate efficient SQL batch queries for any CRUD operations in any flow or Power App.

This transforms Power Apps table data or flow action output JSON data into a type of CSV data. Then the main part of the flow efficiently transforms that CSV-like data into SQL queries that can Create, Read, Update, or Delete all the data within a single SQL Query action. There are no slow-running loops in the app or flow.

Power Apps Test Set-Up & Formula

Example Power Apps Formulas (Anytime I post a certain formula to the page, it breaks and won't let me edit the page anymore. You'll have to use the link or download the added zip file below.)
https://drive.google.com/file/d/1eInULGezXoaxSwsAiairyTBT6FEo9YkN/view?usp=sharing 

(The part with the Power Apps JSON array string regex & conversion for the flow response was taken from this video:https://youtu.be/2xWiFkBf294
This can be replaced with the simpler built-in Parse JSON once that feature is in preview or generally available.)

Power Automate Flow Overview Picture

Example Run Update Section


Thanks for any feedback, & please subscribe to my YouTube channel (https://youtube.com/@tylerkolota?si=uEGKko1U8D29CJ86)

You can download & import the example flow below. 

[kylzbaba]

Thank you for your reply. 

I found an article for blob batch, still going through it https://learn.microsoft.com/en-us/rest/api/storageservices/blob-batch 

Hopefully I can make sense of it because it is a bit too technical for me. 

Is it advisable to save base64 files to an sql database, I always thought it impacted negatively on the performance of the database?

[takolota]

@kylzbaba 

Thanks for sharing that article. That seems like a much better direction to take if you keep working on that in Blob storage.

And yes, performance would likely suffer putting many large base64 document strings into a database.

@Paulie78 know anyone working on more accessible templates for batch actions in blob storage?

[ToddKepler]

I'm bummed.  Got this setup and didn't realize this won't be useable for my on-prem server.

{ "message": "Gateway GetPassThroughNativeQueryMetadataAsync - this operation (Execute Native Sql) is currently not supported using an on-prem gateway connection" }

[takolota]

@ToddKepler 

Sorry.

Maybe now that RPA comes with the $15 premium license, you could set something up to run a desktop flow on your machine or on a virtual machine that is always on & that desktop flow could use the query?

But at that point I don’t know why you wouldn’t just set up a gateway.

[NguyenDHuy]

Hello @takolota, thanks for your work with this flow and i need your help.
The flow version I'm using is v1.8 but it seem like error with data have comma still happen.

Any tip to fix this problem. Should a just replace this \"| or something.

Thanks in advanced.

[NguyenDHuy]

And i found 1 more problem

this quotation mark

[takolota]

@NguyenDHuy If it is not going to affect your data much, you will want to move the SingleQuote action up & set an expression to replace any single quotes in your columns that may have them with an apostrophe ` or something like so...