Need help on Logic apps to enhance the functionality

Question

Need help on Logic apps to enhance the functionality

Rakib 0

Hello, We are using logic apps to fetch the data from the SharePoint list and save it into a SQL database. As I have more than 50,000 rows of items in the SharePoint list, I save them into a SQL database based on the status field. Basically, if the status is equal to "new," then trigger the flow and store it in a SQL database. How can I save 50,000 items at once? Also, I would like to check the database for existing data. If the date, project number, and unit number fields contains same values in the database, then don't store those records. What do I need to change from the below logic apps to accomplish my project? Thanks in advance User's image

1 answer

Your answer

Answer 1

MayankBargali-MSFT 70,936 Moderator

@Rakib Thanks for reaching out.

How can I save 50,000 items at once?

There is no out of box solution to do it without using the for each action and iterating it. In case if you want the single call to the SQL DB then you can Execute a SQL query (V2) action and build your query dynamically inside the for each action iteration. This would have the same number/a greater number of action execution as you are building the query dynamically inside the for each action and outside the action you will pass the dynamic query.

If the date, project number, and unit number fields contains same values in the database, then don't store those records.

The best way would be leveraging the Execute a SQL query (V2) action and in your query do the validated if the records exist or not.

Rakib 0 Reputation points

2024-01-31T22:33:24.55+00:00

Hello, Thanks for your reply. it would be greatly appreciated if you could provide me the steps as i am newbie for Logic apps. FYI using For Each i am fetching items value from SP List and then in the condition checking whether status value is new or not. if new then store into sql database.
MayankBargali-MSFT 70,936 Reputation points Moderator

2024-02-02T03:32:32.3233333+00:00

@Rakib Your existing flow looks good but Instead of Insert row action you need to use the Execute a SQL query (V2) action with the SQL command. Do you need help with writing the SQL command?
Rakib 0 Reputation points

2024-02-02T03:55:42.9233333+00:00

Thanks again MayankBargali. Could you please provide the sql command if possible?
MayankBargali-MSFT 70,936 Reputation points Moderator

2024-02-06T04:06:43.5066667+00:00

@Rakib Sure. Let me do test the SQL command and will update you.
Rakib 0 Reputation points

2024-02-07T16:08:11.4633333+00:00

Hello Mayank, Please have a look at below screen shots for my logic apps. what i am doing wrong as data are not saving into sql database

Executing Query below:

in condition1, i am having below screensho
Rakib 0 Reputation points

2024-02-11T02:10:13.09+00:00

Any update from your end please?
MayankBargali-MSFT 70,936 Reputation points Moderator

2024-02-12T05:14:01.8533333+00:00
@Rakib Have you looked into the run history of any of your run and reviewed whether insert row copy action is getting executed? What is the output of the execute query? As you need to review each action and what is the output and what is the excepted output so you can modify your condition.

As per the screenshot your condition1 is not incorrect and your should validate whether the resultsets Table1 is not null is and greater than or equal to 1 and not validating with individual value.

To check the count the json value returned. Make sure you are updating it to the action name as not directly copy pasting which will return in error. length(body('Execute_a_SQL_query')?['resultsets']?['Table1'])

As per your workflow you are first validating whether the record exists or not and then checking it, but you can directly use the below. The below is just for your reference and you need to update it as per your table name, column and values

IF NOT EXISTS (SELECT * FROM [dbo].[tablename] WHERE FName = '123') BEGIN INSERT INTO [dbo].[tablename] (FName, LName) VALUES ('123','111') END

Let me know if you need help in connecting offline in case if you still facing the issue.
MayankBargali-MSFT 70,936 Reputation points Moderator

2024-02-13T10:02:26.4766667+00:00

Hi @Rakib

Following up to see if my answer and comment helps. Feel free to get back to me if you need any assistance.

Please 'Accept Answer' if it helped so that it can help others in the community looking for help on similar topics.
MayankBargali-MSFT 70,936 Reputation points Moderator

2024-02-16T05:18:49.37+00:00

@Rakib I hope the issue is resolved now. Let me know if you need any further assistance.

Share via

Need help on Logic apps to enhance the functionality

1 answer

Your answer