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:
https://bielite.com/blog/write-back-to-sql-database-from-power-bi/
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
