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

hello guys welcome to jdbots in this video we'll be seeing how to insert rows into the database using the power automate tool and the data which will be coming from the api response yes this is the url and here is the response from the api this contains an array that is trivia categories inside in a day we have many objects each object has an id and name we'll be creating a database with a column id and name and we would be inserting each and every data to our database okay let's get started so i hope you have logged into power automate tool and before that we have to create our database right so i'll create a database and we'll be creating sql database let's give the name as sample db we select the server if you have already created server then you need to select it over here if you have not created one create new server provide the server name the admin login and password fine after you have created the server configure the database the default selection would be standard i'll be using basic one because i don't want to get charged more because this just for the demonstration purpose i'll be having the size of 100 mb and create our server and the database server if you have not created earlier it will create the server and database since my servers is already created it is now creating my database there can be multiple databases in single server it will take a while okay our database is successfully created let's go to the query editor and let's write some queries if you can see there is no table present as of now let's create a table and i would name this table as category and the first column is id and the data type is integer let's keep this as primary key and the second column name was name since name is a keyword so i'll be enclosing inside brackets and it is of data type vector let's give keep it as 50 because there are some category names which have the long names like japanese anime and manga and i would say that this should not be null i should not keep any values inside this column okay we'll be creating an empty table we'll be feeding the data from the power automate tool let's run this query and refresh our database and let's check if our table is appeared under the tables so category table is there with two columns id and name both are not null id is the primary key okay now move on to the power automate tool click my flows create a new flow and it should be our instanced flow because i'll be triggering it manually i'll give the flow name as insert categories in db and how would you like to trigger this flow by manually now this is our trigger now the action part the first step is calling this api i'll copy the url and i'll write the action as http the method is get method because i want to get the meter get the data and uri we have put and that's it since there is no authentication assigned to this api so we don't have to write anything over here https part is done let's initialize some variables i'll rename this because it's a good habit to rename because once you're creating a complex flows then it's really hard to debug or it's really hard to find it out at what place the error is we can easily find out at what place but it will be good practice if you know what that particular action does i'll write this as http call the api call the category api fine before initializing the variable we have to pass this pass the json because the response which will be getting is in the form of json see here this is json response okay before initializing let's delete this part and let's run only this both the steps okay save this flow and let's test as of now flow ran successfully and the output status code is 200 that is that's a successful output and here is the body the body contains our response it contains an array trivia categories inside an arrays we have several objects okay let's copy this whole body we would be needing it to create a schema that we'll see in the next step let's edit this and add a new step pass json and the content is the body see then it shows the dynamic content it shows the output from the previous step previous step output had body headers and status code status code was 200 and the json response is in body so here is the content you can if you you can manually write the schema if you don't know how to write you can generate from the sample will generate it we had copied the json response and will be generating the schema so it has generated schema for us it tells us what are the all the data types the first one was object and second one is array array is this one this is a trivia categories this is of the type arrays and the item contains the objects inside each object we have an integer this was id and we have string which was name now let's initialize some variables and store this data we have the action as initialized variable i'll write this as let's use the same name three way categories will get confused if you use the same name because the dynamic contain content shows the same name so let's use only the categories and it is of data type array we are creating a variable with name categories of type array this is the step we are doing right now and the initial value is trivia categories see the dynamic content shows the output from the previous action there is a path json inside pass json either you can provide the body item id name trivia categories so i'll be using trivia categories since there is an array and i'm storing in categories [Music] and let's rename this one initialize variable categories let's rename this one as well api response now next step will be initializing the id variable and name variable let's name it as id and we it's of type integer i think if you don't want to get confused i'll name this as id var let's rename this initialize variable id we will again initialize name name var which is of type strain and let's rename this one now we have created a variable categories of type array we have created a variable id of type integer then we have created a variable of name name and of type string next is we have to create a variable of type object i'll tell you why we are creating the object type and initialize variable and i will give this as item why i am giving it as item because these are all individual items is just the name i am giving the item will contain all the objects individual objects inside objects we have both the variables and the type is object let's rename this one i think till now it is fine with you all now we will iterate through each object first of all we have to iterate through categories as you have seen we have initialized categories with trivia categories now our next step is control inside control we have applied to each it's in for loop and the output from previous step will be using categories under variables we'll be using categories because categories is initialized with trivia categories and let's let's now set the variable and the variable will be setting is item so inside categories we are setting this object into this variable item and the value is if you scroll at the last this is the output from applied to each current item our for loop will be trading through this array for each object it will set the variable item so the item value will be changing for each iteration now till here i think it is fine let's run this one first and i'll show you what is the output of item variable let's see the past json one where we have initialized the trivia categories to the variable categories so this is the whole array which got initialized to categories and we are iterating through this array and let's see the output inside apply to each when we open the set variable the first object was id name this gets saved into variable item this was the first object if you go to the next iteration there is a second object and so on there total 24 objects and each time the item value is changing let's edit and continue oh i forgot to show you something let's go back and see the last successful run so the value is in json now the value of item is in json format so can we use again pass and get the value of id and name yes we can so i'll again use parse method let's just rename this one set variable item and this pass json item and the content is the item and the schema is we can generate the schema we can copy the json from here and paste it over here so it generated a schema for us and the next step let's set the variable id with value from the previous action previous action is pass json item inside pass json item we see the dynamic content as body id and name body will content both the values id and name so we select the id remember for each iteration the id value gets changed and for each iteration the id variable value will also change set variable id let's set our name variable with the value name let's rename this as well now all our content and data is ready now the final step to insert the row let's use the connector sql server inside sql server we have so many options and we'll be using insert row for the first time users if you're using this connector for the first time you need to create a new connection for creating the connection you have to click on this top right triple dot and add a new connection select the azure id because we are using the azure db and sign in select the server name there seems to be some connection issue but yeah this is the process you have to select the server name database name and the table name i'll use my already created connection and i'll select the server name database name and the table name table name is category we had created once you select the table it will show the columns and the id should be the id variable which we have already set in the previous action and the name is name variable which we already said in the previous two actions perfectly fine this is our whole flow let's save and run before that let me show you by running the select query to see the data currently there are no rows available let's run this one i'll perform the trigger action because we are using the manual trigger we need to sign it to the sql server continue run flow your flow is running these all steps got completed and this is on the final step floor and successfully let's see the output the table name is category and the for the first iteration id is 9 and the name is general knowledge let's check out our database rerun this query i think there seems to be some issue let me refresh our database select top 1000 rows and run the query wait a second oh so the problem is the data went to the jd bots db this is my another db because i had created a connection with this tv i have not created a connection for the sample db let's check that db whether that db is updated or not we have the table category select top thousand rows and voila here we have all the data i think this is the fastest and easiest way to feed the database from any api okay i hope you find this useful thank you all

Channel: JD Bots

Views: 2,442

Keywords: Power Automate, RPA, Azure, SQL Database, API

Id: QlcapElsRyQ

Length: 26min 28sec (1588 seconds)

Published: Thu Sep 10 2020