cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
PaulCullivan
Level: Powered On

How to insert more than 100000 rows into SQL

Hello,

 

Apologies if this has been stated somewhere before.

 

I'm working on a Flow that, once a day, will query a Kusto table, get the query, check to see if it got any results, and if it did insert them into a SQL database.  The problem is that Apply To Each has a hard limit of 100,000 and there doesn't appear to be any way to work around this (timeouts, batch operations, etc.) natively in Flow.

 

I completely understand concerns about load and why you would want to limit operations, but I was hoping that you would be able to recommend some approved way where I could time out or batch this within Flow that you would support. 

 

Or if this has already been answered.  Smiley Happy  I'm certainly not the first person to work with this size of data in Flow.

 

Thanks in advance for your help here.

3 REPLIES 3
Super User
Super User

Re: How to insert more than 100000 rows into SQL

Hi @PaulCullivan 

 

As you have mentioned in the post there is a limit of 100K for the apply to each action in flow. Can you please share how many items do you want to work with here? 

 

You might want to try this approach: Sd2.PNG

 

Sd3.PNG

I am initialising a variable and checking the value to exit from a loop and start a new one. You can change the '100K' value in the do until loop to the difference items remaining (if it is less than 100K) and if it is more, add another set of that. Makes Sense?

 

Hope this Helps!

 

If this reply has answered your question or solved your issue, please mark this question as answered. Answered questions helps users in the future who may have the same issue or question quickly find a resolution via search. If you liked my response, please consider giving it a thumbs up. THANKS!

PaulCullivan
Level: Powered On

Re: How to insert more than 100000 rows into SQL

Appreciate the response.  I thought of something like that - my method was more clunky than yours, appreciate that - but I was hoping for something out of the box.  Being as this is a slow week I am hoping that I have time to get to this today or tomorrow and give this a shot.

 

Thanks again.

PaulCullivan
Level: Powered On

Re: How to insert more than 100000 rows into SQL

In your example, how would you make sure that the iteratior didn't start over from the beginning in the second do until loop?

Helpful resources

Announcements
firstImage

Microsoft Business Applications Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

firstImage

Watch Sessions On Demand!

Continue your learning in our online communities.

Power Platform 2019 Release Wave 2 Plan

Power Platform 2019 Release Wave 2 Plan

Features releasing from October 2019 through March 2020

thirdimage

Flow Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

fifthimage

Microsoft Learn

Learn how to build the business apps that you need

sixthImage

Power Platform World Tour

Find out where you can attend!

seventhimage

Webinars & Video Gallery

Watch & learn from the Flow Community Video Gallery!

Users Online
Currently online: 231 members 4,590 guests
Recent signins:
Please welcome our newest community members: