Insert Rows in the Azure SQL DB Table using Power Automate | Send API Response Data to DB

Azure SQL Database is the intelligent, scalable, relational database service built for the cloud. It’s evergreen and always up to date, with AI-powered and automated features that optimize performance and durability for you. Serverless compute and Hyperscale storage options automatically scale resources on-demand, so you can focus on building new applications without worrying about storage size or resource management.

Welcome! What can you expect from Power Automate? Here are a few examples of what you can do:

Automate business processes
Send automatic reminders for past due tasks
Move business data between systems on a schedule
Connect to almost 300 data sources or any publicly available API
You can even automate tasks on your local computer like computing data in Excel.

For more information refer Microsoft documentation here.

Objective

Even we have picked up a similar use case. Here we will be updating the SQL Database with data coming as a response from an API using Power Automate.

It is not necessary to use Azure SQL DB only, you can make use of any other DB as well which can be on the cloud or on-premises. Power Automate has the capability to connect and update them.

Pre-Requisites

Create Azure SQL Database

Here we will try to insert rows into the database using Power Automate Tool. The data will be fetched from the API response.

Let’s start by creating database with columns id and name. We will be creating Azure SQL Database.

If a new server is not created. You can click on Create new option and type in new credentials to create a new server. After you have done this. Configure the database to Basic -> 100MB just for learning purposes. If your requirement is large, then you can select accordingly.

After the server is created, please click on Review + Create. There can be multiple databases in single server.

In the Database create a new table naming it as “Category” and add two columns named id and name. We will be creating an empty table and filling the data with the Power Automate tool. Right-click on Tables and select the top 1000 rows to check if the table created is empty.

create table [Category]
(
    [id] int primary key,
    [name] varchar(50) not null    
)
go

Create Power Automate Flow

Log in to the Power Automate tool by going to https://flow.microsoft.com and click on My Flows and create a new flow selecting Instance flow- from blank.

Give the flow name as Insert Categories in DB and select Manually trigger a flow. There are many triggers present, you can select based on your requirement. For testing, I have used the Manual trigger.

Let’s add a new Action HTTP. You might get a pop up to start trial. Click on that to continue.

The method is GET. We will be using https://opentdb.com/api_category.php as URI. This API gets the quiz categories with their IDs. It is a public API with no authentication and body.

You can also rename the HTTP by clicking on the kebab menu -> Rename. As you work with complex flows it becomes easy for identification.

Just for your verification you can the test the flow just to check if API call is working.

To test click on I’ll the perform the trigger action -> Test -> Run flow.

If your flow ran successfully. Great, you’re going well. Click on the HTTP to verify the output status code. The body contains our response. Copy the response in a text file. We will use it to parse.

The next step is Parse JSON. Select Body from Dynamic content for Content field in Parse JSON. In the Schema section, select Generate from the sample and paste your API response to generate a schema.

Now let’s Initialize some variables –

Name: categories
Type: Array
Value: From dynamic content select trivia_categories.

trivia_categories is an array from API response and that array portion, we are putting it in categories variable.

Initialize another variable –

Name: id
Type: Integer

Leave value blank. We will be setting it later.

Initialize another with name_var and type String.

Initialize variable item of datatype Object.

The next step is to add a Control -> Apply to each. Here previous output we will be using categories. Next click Add an action where we set the variable item and current item as its value.

You can save and run this flow for testing whether till now everything is set right. For verifying click on initialize variable – categories where the whole array gets initialized to categories iterating through the array.

Under Apply to each when you set variable option you will get 24 objects and each time item value which is JSON format is changing. Let’s continue.

Next action – Parse JSON where the content is item and schema can be generated using the output from set variable Item.

Set the variable id-var with value id from the json.

Next set name variable with value name.

All our content is ready, the final step is to insert the row in DB. Make sure you have created the connection while adding the Insert Row connector from SQL Server.

Click on kebab menu add a new connection and sign with the required details.

Then after the above details are inserted, automatically it will ask for id and name values. Select the respective variable. You’re all set for the flow run.