Import Web API into sql server table every minute easily

Hello ,

Im trying to find a quick easy way to import the results from a Web API into a sql server database , which will run every minute?

the example of the API is the following   

Can anyone advise on this please?

Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
what version of MS SQL are you using?

are you consider to use SSIS or a custom ETL program to import the data to MS SQL?
Pawan KumarDatabase ExpertCommented:
You can create a stored procedure

and call the stored procedure using SQL Job with 1 minute interval.
deanmachine333Author Commented:
Hello Ryan,

I prop be using SQL Server 2016 and i could use SSIS , im just trying to see what the quickest and easiest way to get the output from the API into a sql server table.

Ive seen this product online which is what im looking to achieve
SolarWinds® VoIP and Network Quality Manager(VNQM)

WAN and VoIP monitoring tools that can help with troubleshooting via an intuitive web interface. Review quality of service data, including jitter, latency, packet loss, and MOS. Troubleshoot call performance and correlate call issues with WAN performance for Cisco and Avaya calls

Pawan KumarDatabase ExpertCommented:
SQL Server Proc will be the fastest. SSIS will also internally call SQL only.
deanmachine333Author Commented:
@Pawan hiya, can you advise using the example API how the code would be achieved, please?
Pawan KumarDatabase ExpertCommented:
In fact you can the web API directly from SQL Server itself. See if this works for you
deanmachine333Author Commented:
@Pawan can you advise the sql code to achieve this please?
Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
you may customize and do some test from the methods mentioned in this article:

Consuming JSON Formatted API Data in 2016
Pawan KumarDatabase ExpertCommented:
sample code for you -

CREATE PROC CallWebService
	 @strSerialNumber VARCHAR(50)
	,@strPassword VARCHAR(50)
	,@strUserID VARCHAR(50)
	,@strPostcode VARCHAR(50)
	,@strProperty VARCHAR(50)
	,@strCountyType VARCHAR(50)

	Declare @Ob as Int, @ReturnData as Varchar(MAX);
	SET @WebU = @WebU + @strSerialNumber + '&strPassword=' + @strPassword + '&strUserID=' + @strUserID + '&strPostcode=' + @strPostcode + '&strProperty=' + @strProperty + '&strCountyType=' + @strCountyType
	Exec sp_OACreate 'MSXML2.XMLHTTP', @Ob OUT;
	Exec sp_OAMethod @Ob, 'open', NULL, 'get', @WebU,'false'
	Exec sp_OAMethod @Ob, 'send'
	Exec sp_OAMethod @Ob, 'responseText', @ReturnData OUTPUT
	Select @ReturnData
	Exec sp_OADestroy @Ob


Open in new window

Daniel_PLDB Expert/ArchitectCommented:

If you want to use code from inside SQL Server by using Ole Automation you need to enable it first, by default it is disabled.
sp_configure 'show advanced options', 1;  
sp_configure 'Ole Automation Procedures', 1;  

Open in new window

However, I suggest to use Powershell type of SQL Server Agent Job. I find it more stable and secure than going out with Ole Automation.

David Johnson, CD, MVPOwnerCommented:
powershell script
#requires -version 3.0

function Add-APIData
    Describe purpose of "Add-APIData" in 1-2 sentences.

    Add a more complete description of what the function does.

    .PARAMETER server
    Describe parameter -server.

    .PARAMETER database
    Describe parameter -database.

    .PARAMETER text
    Describe parameter -text.

    Add-APIData -server Value -database Value -text Value
    Describe what this call does

    Place additional notes here.

    URLs to related sites
    The first link is opened by Get-Help -Online Add-APIData

    List of input types that are accepted by this function.

    List of output types produced by this function.

    [Parameter(Mandatory=$true,HelpMessage='Add help message for user')]

    [Parameter(Mandatory=$true,HelpMessage='Add help message for user')]

    [Parameter(Mandatory=$true,HelpMessage='Add help message for user')]
$scon                    = New-Object -TypeName System.Data.SqlClient.SqlConnection
  $scon.ConnectionString = ('SERVER={0};DATABASE={1};Integrated Security=true' -f $server, $database)
  $cmd                   = New-Object -TypeName System.Data.SqlClient.SqlCommand
  $cmd.Connection        = $scon
  $cmd.CommandText       = $text
  $cmd.CommandTimeout    = 0

$nl   = "`n"

$json = (Invoke-WebRequest -Uri ) -join "`n" | ConvertFrom-Json
$add  = @()
foreach ($r in $json.result)
  $add += "INSERT INTO tb_FRED_WTI VALUES ('" + $r.MarketName + "','" + $r.High + "','" + $r.Low + "','" + $r.Volume + "','" +`
  $r.Last + "','" + $r.Volume + "','" + $r.Last + "','" + $r.BaseVolume + "','" + $r.TimeStamp + "','" + $r.Bid + "','" +`
  $r.Ask +  "','" + $r.OpenBuyOrders + "','" + $r.OpenSellOrders + "','" + $r.PrevDay + "','" + $r.Created  + "')" + $nl
Add-APIData -server 'TIMOTHY\SQLEXPRESS' -database 'MSSQLTips' -text $add

Open in new window

original source:
updated to use data from Question

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
deanmachine333Author Commented:
@david thanks i got it working with that code ( few mods but working huge thanks ) what would you advise for me to run the powershell script every minute?
David Johnson, CD, MVPOwnerCommented:
deanmachine333Author Commented:
thanks got it working with Davids solution.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.