Copy the records from Sharepoint list to SQL table

(0) Share

Report

Posted on by vnmed

Hi guys,

I'm trying to set up a flow which copy the records from a Sharepoint list to a SQL table.

Current flow: trigger is When an item is created or modified action, then I load the data from both tables, then I'm using the Condition action to check if a record already exists in the SQL table, if yes then update row if not then insert row.

My PK on SQL table is InputCommentKey

Apparently my Condition action is not set up correctly. I'm looping through the items on SP list and looping through the items on SQL table, if a SP list key is equal to SQL table key, then update row, else insert row.

But this is not working correctly.

Can you please help how to copy the data from SP list to SQL table?

thank you

All responses (8)

Answers (1)

vnmed 10 on at

Like (0)

Report

Re: Copy the records from Sharepoint list to SQL table

Hi @Expiscornovus

to insert the single quotes around has solved this, now is working correctly.

Thank you so much for your help!
Expiscornovus 31,891 Most Valuable Professional on at

Like (0)

Report
Re: Copy the records from Sharepoint list to SQL table

Hi @vnmed,

I see in your static example you have used single quotes, because the InputCommentKey is of type text and not a number, which makes sense.

In that case the dynamic filter also needs to have that single quotes around the value. Try the below:

InputCommentKey eq '@{triggerBody()?['InputCommentKey']}'
vnmed 10 on at

Like (0)

Report

Re: Copy the records from Sharepoint list to SQL table

Hi @Expiscornovus

yes, this makes sense now, I was not aware that each change will have it's own flow run instance.

I've been testing this solution and there is one thing which makes this flow failing.
It's the Filter Query in the Get rows (v2).
When I use the filter query field is equal to field from a trigger, then it always return the null values, no output from Get rows (v2)

But when I use that the field is equal to the static value, then it's working. The output of Get rows has body values.

Do you have any idea what kind of issue it can be?
Both datatypes are text respectively varchar in sql table.

thank you
Expiscornovus 31,891 Most Valuable Professional on at

Like (0)

Report
Re: Copy the records from Sharepoint list to SQL table

Hi @vnmed,

You flow triggers whenever an item is created or modified. So, I don't see a reason to go through all your SharePoint items again (the trigger action already makes sure it will capture every change). Each change will have it's own flow run instance. You would only have to check it for that latest change, all previous changes should already be handled by the previous flow run instances. I hope that logic makes sense?

Looping through all items only makes sense to me if your trigger action is a recurrence action (when you only run the flow 1x day on a schedule). In that case I would suggest to compare all items.

Regarding the ID field in my Filter Query, this was just an example in my SQL CustomTable. In your case that would probably be (assuming it is a number)

InputCommentKey eq @{triggerBody()?['InputCommentKey']}
vnmed 10 on at

Like (0)

Report
Re: Copy the records from Sharepoint list to SQL table

Hi @Expiscornovus ,

indeed there is no special reason to retrieve all the items from the Sharepoint list, but this trigger for SharePoint lists "When an item is created or modified" returns only one record at a time.
I have added Get items action in order to loop through all the items in SP list and compare the items from SP list with the items from SQL table. If there is a match on PK, then update, if not then insert.

I have 2 questions to your solution:
- Can you please specify which field from which table you mean by 'ID' in the expression?:
ID eq @{triggerBody()?['InputCommentKey']}
In the Get rows(v2) I cannot choose anything except the fields from previous action 'When an item is created or modified'
Btw. in both SP list and SQL table the PK is named 'InputCommentKey'

- How/where do I loop through all the items updated/created in Sharepoint list?

thank you
Verified answer

Expiscornovus 31,891 Most Valuable Professional on at

Like (0)

Report
Re: Copy the records from Sharepoint list to SQL table

Hi @vnmed,

In that case lets have a look at your cloud flow approach. First question about the current setup. What is the reason you are also retrieving all items via the Get Items action?

You should already have the details in your trigger action to be able to compare it with your existing rows in your SQL table. You should be able to use the Filter Query field of the SQL Get rows for that. In the Condition you can check if that Get rows returns more than 0 items. If that is the case it already exists and you can update your matching row.

Below is an example

1. Filter Query in the Get rows (v2)

ID eq @{triggerBody()?['InputCommentKey']}

2. Check if any matches found

length(outputs('Get_rows_(V2)')?['body/value'])

is greater than 0

3. Retrieve the ID of the first found match in the ID field of the Update row (v2) action

first(outputs('Get_rows_(V2)')?['body/value'])['ID']
vnmed 10 on at

Like (0)

Report

Re: Copy the records from Sharepoint list to SQL table

Hi @Expiscornovus ,

thanks for your suggestion, but in this case I need to load the data into on premise SQL DB table.
Expiscornovus 31,891 Most Valuable Professional on at

Like (0)

Report

Re: Copy the records from Sharepoint list to SQL table

Hi @vnmed,

Is the target an Azure SQL Database? In this instance I might suggest to have a look at Dataflows (Gen2) feature of the Microsoft Fabric Data Factory instead of using a Power Automate cloud flow for this?

With Power Query you can easily load data into other systems and it is very good and mapping, transforming and loading. I can recommend looking into this to see if that might be a better fit for your use case:

https://learn.microsoft.com/en-us/fabric/data-factory/create-first-dataflow-gen2

It also has settings for what to do in case of updates, handling schema changes and column mappings. Logic you have to create yourself in a cloud approach.