SQL Server Store Procedure to Fill Table with a Web API

README.md

SQL Server

This script was made by Steven Checo

How it works

• You need to run the following code once, before create the stored procedure

sp_configure 'show advanced options', 1;   
GO   
RECONFIGURE;   
GO
sp_configure 'Ole Automation Procedures',1;
GO
RECONFIGURE;
GO

• Create your table with the fields you need
• Where the script says Field_One and Field_Two is an example for the tables Fields that you want to fill
• Where the script says PropertyNameOne and PropertyNameTwo is an example of the property names of the API, the data type must be tha same of each field you declared to fill before

WebAPIField.sql

USE [DBNAME]
GO
/****** Object:  StoredProcedure [dbo].[TABLE]    Script Date: 3/3/2020 12:47:23 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Steven Checo Terrero>
-- =============================================
CREATE PROCEDURE [dbo].[PROCEDURE_NAME] 
	-- Add the parameters for the stored procedure here
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Declaring var objects to get the data from the web API, Object as INT for the result and @ResponseText as Table
	--To Simulate a Table for a JSON
	Declare @Object as Int;
	Declare @ResponseText as table(Json_Table nvarchar(max));

	--This is the Code for open the WEBAPI
	Exec sp_OACreate 'MSXML2.XMLHTTP', @Object OUT;
	Exec sp_OAMethod @Object, 'open', NULL, 'get',
					 '<Your Web API URL>', --Your Web API Url (invoked)
					 'false'
	Exec sp_OAMethod @Object, 'send'
	Exec sp_OAMethod @Object, 'responseText', @ResponseText OUTPUT

	INSERT into @ResponseText (Json_Table) exec sp_OAGetProperty @Object, 'responseText'

	INSERT INTO dbo.TABLE_NAME ([Field_One],[Field_Two]) SELECT * FROM OPENJSON((select * from @ResponseText))
	WITH (   
		  [PropertyNameOne] nvarchar(max) N'$.PropertyNameOne',
		  [PropertyNameTwo]   nvarchar(max) N'$.PropertyNameTwo'    
	)

	Exec sp_OADestroy @Object
END
GO

Super :D