asked on 11/16/2017

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

https://bittrex.com/api/v1.1/public/getmarketsummaries

Can anyone advise on this please?

Thanks

Databases Microsoft SQL Server REST SQL

Last Comment

deanmachine333

11/16/2017

Ryan Chong

11/16/2017

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 Kumar

11/16/2017

You can create a stored procedure

and call the stored procedure using SQL Job with 1 minute interval.

deanmachine333

11/16/2017

ASKER

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
https://api-db.com/

Pawan Kumar

11/16/2017

SQL Server Proc will be the fastest. SSIS will also internally call SQL only.

deanmachine333

11/16/2017

ASKER

@Pawan hiya, can you advise using the example API how the code would be achieved, please?

Pawan Kumar

11/16/2017

In fact you can the web API directly from SQL Server itself. See if this works for you https://www.mssqltips.com/sqlservertip/3495/extracting-api-data-using-powershell-and-loading-into-sql-server/

deanmachine333

11/16/2017

ASKER

@Pawan can you advise the sql code to achieve this please?

Ryan Chong

11/16/2017

you may customize and do some test from the methods mentioned in this article:

Consuming JSON Formatted API Data in 2016
http://www.sqlservercentral.com/articles/JSON/141175/

SOLUTION

Pawan Kumar

11/16/2017

THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.

View this solution by signing up for a free trial.

Members can start a 7-Day free trial and enjoy unlimited access to the platform.

See Pricing Options

Start Free Trial

Daniel_PL

11/16/2017

Hi,

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;  
GO  
RECONFIGURE;  
GO  
sp_configure 'Ole Automation Procedures', 1;  
GO  
RECONFIGURE;  
GO

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.

Regards,
Daniel

ASKER CERTIFIED SOLUTION

David Johnson, CD

11/16/2017

THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.

View this solution by signing up for a free trial.

Members can start a 7-Day free trial and enjoy unlimited access to the platform.

See Pricing Options

Start Free Trial

deanmachine333

11/16/2017

ASKER

@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?

SOLUTION

David Johnson, CD

11/16/2017

THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.

View this solution by signing up for a free trial.

Members can start a 7-Day free trial and enjoy unlimited access to the platform.

See Pricing Options

Start Free Trial

deanmachine333

11/16/2017

ASKER

thanks got it working with Davids solution.