Writing data from API to SQL Server: Power Query vs. Power Automate vs. Data Factory

michaelshparber — Sun, 27 Sep 2020 11:24:25 GMT

What is the best way to insert data that was extracted from API calls to SQL Server database?

I've built a Power Query that extracts API data with nested calls (using functions).

It works fine, but I need to save the data to SQL tables.

I've seen this excellent blog on how to INSERT with Power Query:

But I am wondering if it is the best way to do it.

I am considering other platforms as well:

- Data Factory / Azure Synapse

- Power Automate

I am not a developer (though I work with Power Query including functions), I know basic API, good at copy-paste from google 🙂

Data Factory seems rather complex to me.

Power Automate seems a good fit, but I couldn't find how to do NESTED API CALLS and upsert to SQL.

My requirement are:
- Dynamic nested calls, for example //uri/students --> //uri/[StudentID]/classes --> //uri/[StudentID]/[ClassID]/attendance

- Incremental refresh: Upsert to SQL using PK, or, alternatively, delete all the records that are in the current API and insert all API data again.

- Pagination

I feel Power Automate can be a solution i am looking for

I would like to hear your professional opinions and if you have examples - will appreciate a lot!

Thank you.

Michael

topic Writing data from API to SQL Server: Power Query vs. Power Automate vs. Data Factory in Developer