Skip to main content

Notifications

SQL Batch Create Read Update and Delete

takolota1 Profile Picture Posted by takolota1 4,781

SQL Logo.png


If you just need to insert data into SQL, you are likely better off with a Stored Procedure: https://powerplatform.fi/how-to-insert-json-from-power-automate-to-azure-sql/


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

PwrAppFormulaV1.5P1.png
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

takolota_0-1661614045674.png

 


Example Run Update Section
BatchSQL4.png

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

 

You can download & import the example flow below. 


Solution Zip Download Link: https://drive.google.com/file/d/1MFIwjK6eNLAho_e-8RT0OX_0ou9o2v2U/view?usp=sharing

Legacy Power Automate Import Zip Download Link: https://drive.google.com/file/d/1MX4QRnoaZGgJTnZFv9Vu2jMIeqc9cE4A/view?usp=sharing

Categories:

Comments

  • takolota1 Profile Picture takolota1 4,781
    Posted at
    SQL Batch Create Read Update and Delete

    @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...

    ReplaceSingleQuote.png

  • NguyenDHuy Profile Picture NguyenDHuy
    Posted at
    SQL Batch Create Read Update and Delete

    And i found 1 more problem

    NguyenDHuy_0-1715423225733.png

     

    this quotation mark

    NguyenDHuy_1-1715423359190.png

     

  • NguyenDHuy Profile Picture NguyenDHuy
    Posted at
    SQL Batch Create Read Update and Delete

    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.

    NguyenDHuy_0-1715409718993.png

    NguyenDHuy_1-1715409748223.png

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

    Thanks in advanced.

  • takolota1 Profile Picture takolota1 4,781
    Posted at
    SQL Batch Create Read Update and Delete

    @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.

  • ToddKepler Profile Picture ToddKepler
    Posted at
    SQL Batch Create Read Update and Delete

    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" }

  • takolota1 Profile Picture takolota1 4,781
    Posted at
    SQL Batch Create Read Update and Delete

    @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?

  • kylzbaba Profile Picture kylzbaba 182
    Posted at
    SQL Batch Create Read Update and Delete

    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?

  • takolota1 Profile Picture takolota1 4,781
    Posted at
    SQL Batch Create Read Update and Delete

    @kylzbaba 

     

    I have not made anything like that. And I’m actually not sure if there is even a technical possibility for a similar batch set up on Microsoft systems at this point.

    If you’re curious about it, you could look for any batch document API call options for Azure Blob &/or for Dataverse.

     

    And if you really really needed batch actions for document storage, then you could try setting something up to send the document base64 as strings to the dataset, then have any front-end or viewing mechanism convert the base 64 back to a document. But that seems needlessly complicated & the strings would be really long for the documents, so you would still likely have small batch sizes.

  • kylzbaba Profile Picture kylzbaba 182
    Posted at
    SQL Batch Create Read Update and Delete

    Hello Takolota,

     

    Do you have a similar solution for Azure Blob ? 

  • takolota1 Profile Picture takolota1 4,781
    Posted at
    SQL Batch Create Read Update and Delete

    Version 1.7

    I unnecessarily added something to try to handle in-data comma errors and it actually caused an error in the batch create & batch delete. I removed this additional buggy expression because in-data comma errors should already be avoided in SQL through the single-quotes ' ' around each string-based value. 
    Then the Create CSV action creates some errors when it adds double quotes around any item with a comma in it, so I added some replace expressions to handle that in the DataLines action.