Comparing multiple SQL columns to JSON response from HTTP request

[alltheabove]

I am working on a flow that updates a SQL table based on information that is pulled from a JSON response to an external API.

There are around 45 fields in the JSON response and around 25 in the SQL table with several thousand entries in the SQL table, and unfortunately the SQL table using different naming conventions than many of the field names from the JSON.

There are a number of different actions that I need to do based on differences between the two. For example, if an entry from the JSON response does not exist in the SQL table I need to create a new entry based on the information in the JSON.

I also need to make sure that any field in the JSON response that has been updated also gets updated in the SQL table, but I not sure the best way to check all of the fields for every entry.

My current approach involves a number of nested conditions checking all of the fields in the SQL table against fields from the JSON response but this feels extremely clunky and inefficient.

I was wondering if anyone had better suggestion for how to accomplish the kind of conditional updating I am trying to accomplish between the the SQL and the JSON so that I can loop through all of the entries in a reasonable amount of time.

Any help would be greatly appreciated!

[wskinnermctc]

I needed to do something similar except going from SQL to SharePoint. Instead of individually checking items, I just batch update everything. And then create any items that aren't on the list.

Batch update isn't ideal because it looks every item is changed everyday. However, I wasn't really trying to track changes, I just wanted the list updated. It would be nice to be able to look back and see what changed using versions, but it wasn't necessary.

Could you go for a batch update and just update all items?

[takolota]

@alltheabove & @wskinnermctc 

You could also add a Modified column to the SQL table that auto-updates with the current time any time something is changed in the record. That way you could then filter to just the items modified since the last update & you would know those are the only records that need to be updated in your other table.

[alltheabove]

@takolota& @wskinnermctc 

Thank you both for the responses.

I am sorry if I was not more clear but I am pulling the information from an API and updating the SQL table based on the information in the JSON response from the HTTP request.

The SQL table also includes many older rows of data that have not been deleted or cleaned up but are no longer necessary to update that I would like to ignore, so I do not believe sorting by update time would be an effective solution.

Any other ideas?

[wskinnermctc]

Sorry @alltheabove I think my reply put things off track because I was going from SQL to SharePoint List. And @takolota was kind of referring to my issue and not yours.

However, just like he was describing to me about changing my SQL table, is there anyway you can change or adjust the source of the API to help make it easier to call only the items that need to be updated?

I still think that you could batch update everything in your SQL table from your JSON.

We have some tables in our SQL Server that we completely wipe and repopulate each day because it is faster and less complicated than trying to do individual changes.

You could also extend on that idea where you have a table in your SQL server that is like "HoldingJSONDataTable" and you write everything into it. Then you call a stored procedure that will do the check and update between your "HoldingJSONDataTable" and your "MainSQLTable". Then the stored procedure clears the "HoldingJSONDataTable" so that it is ready for the next time data is loaded from your flow.

This puts the heavy work into the SQL Server and the Power Automate flow is only pushing the data into the HoldingTable and calling stored procedures.

Sorry I don't have an easy Power Automate solution, it is not easy to compare and update large tables of data.

[takolota]

That’s more clear. So you may want to update all the SQL rows with whatever data the API gives if you can’t reference something similar for recently modified records in the API.

I suggest using a Select action, inputting the SQL table outputs in the From field, clicking the right-side button to change it to a single-input Map box, & using the dynamic content for the SQL primary key in that Map input.

That should get an array of just the primary key values from SQL.

Then use a Filter array on the API outputs & set the condition to 

SelectOutputs contains DynamicContentOrExpressionForPrimaryKeyOfAPI

That will return a list / JSON array of just the API records that need to be updated in SQL.

From there you can query SQL for each primary key, then update the record.

Then use a Filter array on the API outputs & set the condition to 

SelectOutputs contains DynamicContentOrExpressionForPrimaryKeyOfAPI

That will return a list / JSON array of just the API records that need to be created in SQL.

From there you can just create the new SQL records.

[takolota]

@wskinnermctc By the way, I did just update my SharePoint Batch Update template to add creates/upserts, simplify the set up, increase the capacity to SP lists with a couple million records, and make it even faster: https://powerusers.microsoft.com/t5/Power-Automate-Cookbook/Batch-Update-Create-and-Upsert-SharePoin...

That might be helpful, especially if you ever want to move to incremental updates of just newly modified data instead of deleting & recreating entire lists.