cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
dd96
Frequent Visitor

perform bulk deletion for SQL database

I have built an app for few users and one of the functions is to delete records from SQL database table.  I have tried to use ForAll and Remove IF functions, but these two functions delete records one by one and takes too much time for huge record deletion.

 

wondering how can i delete 2000 records from SQL table via powerapps quickly. 

Please provide your suggestions. 

4 REPLIES 4
Drrickryp
Super User
Super User

@dd96

It's easier to do it in sql and more efficient

cha_cha
Resident Rockstar
Resident Rockstar

Hello @dd96 

 

The ForAll function would be not good if you have more 2000+ data.

 

What you can do is create a flow that will run your delete SQL script.

 

cha_cha_0-1669334917450.png

cha_cha_1-1669334990529.png

 

 

As for best practice, you should make stored procedures for it. This is just an extra option. you can always settle for a simple query in Power Automate.


 
Just in case you my answer helped you solve your problem, please mark/accept this as a SOLUTION This helps community members if they experience a similar issue in the future.

 
bistek.space   @cha_bistek    @BisTekSpace 
dd96
Frequent Visitor

Thanks for the response. I was thinking the same and let you know how it goes. 

thank you 

dd96
Frequent Visitor

I am able to implement bulk deletion by stored procedure and power automate. 

 

stored procedure:

create procedure BulkDeletion 

@ID int 

as 

begin 

   delete from table_name 

    where tableid = @ID 

 end 

go 

 

Power Automate Flow:

 

dd96_0-1669820221286.png

 

Power app 

 

on Delete icon for each item: 

Collect(collection_delete,[ThisItem.Id]);

it collects unique ids and will remove items from collection only, not from database 

 

 

on Save button where actual deletion will be performed : 

 

Set(testflowresult,ForAll(collection_deletion,testflow.Run(collection_deletion[@Value]).status));

 

set will wait for the flow's response and will not execute next commands until it finish 

 

I was having issue with openjson otherwise it is a good option too. 

 

 

 

 

 

Helpful resources

Announcements
Power Apps News & Annoucements carousel

Power Apps News & Announcements

Keep up to date with current events and community announcements in the Power Apps community.

Community Call Conversations

Introducing the Community Calls Conversations

A great place where you can stay up to date with community calls and interact with the speakers.

Power Apps Community Blog Carousel

Power Apps Community Blog

Check out the latest Community Blog from the community!

Top Solution Authors
Top Kudoed Authors
Users online (3,816)