Migrate Data from an API in Azure DB using Power Automate
2. Set the frequency of the Flow depending on your requirement. I will be setting 1 Day.
3. Select an HTTP connector
4. Select the Get in the Method and your API URL in the URI. Select the authentication type.
5. Once you have selected the API, select the Parse JSON for parsing the API data.
6. Select the body as Content. For schema, click on Generate from sample.
7. Copy the sample of your payload here.
8. Click done to generate the schema.
9. Now let’s Initialize some variables:
Name: data ; Type: Array ; Value: From dynamic content select Parse JSON.
data is an array from API response and that array portion, we are putting in the JSONdata variable.
10. Initialize another variable, Item from the above-highlighted schema of type object.
11. Initialize another variable, StudentId of type Int. This is the Primary key of our Student Table.
12. Initialize another variable, Name of type String. Select Control to insert the data of the API in the loop.
领英推荐
13. Select Apply to each to iterate the API data.
14. Select the Set variable to set the API current item.
Create Azure SQL Database
15. We will insert rows into the azure database using the Power Automate Tool. If you have an existing one, you can use that. Let’s start by creating a sqlserver/database from the start.
16. Add the Server Login and Password for your database and set the database admin by selecting the Set admin.
17. We will create Azure SQL Database StudentDetails.
18. Create a table StudentDetails by selecting the Query editor from the left menu with columns StudentId and Name.
19. After refresh, we can see the newly created table.
20. Now come back to Power Automate to insert the API response into the SQL after parsing the Current Item from the above step. Add the Parse JSON - data into Azure Database.
21. Save and run the Flow. It will take a few seconds to complete the flow if you have more extensive data from the API.
22. After the flow is completed, navigate to the Azure database table to see the inserted records from Power Automate.