Post reply

How to Insert API data into database using Powershell

  • May 5, 2023 at 8:35 pm

    #4184956

    Hello,

    I am trying to teach myself how to INSERT data from an API into a SQL Server DB using PowerShell.  For this exercise, I am using a public API which is: https://date.nager.at/api/v2/publicholidays/2020/US.  What I would like to do is insert this into a table using a Stored Procedure.  Below is my code.  I think I need to create a loop but not sure how to do this, can someone help me complete my code example?

     

    $Server = "xxxx" 
    $Database = "API_Test" 
    $SqlConn = New-Object System.Data.SqlClient.SqlConnection("Server=$Server;Database=$Database;Integrated Security=True;")
    $SqlConn.Open()
    $cmd = $SqlConn.CreateCommand()
    $cmd.CommandType = 'StoredProcedure'
    $cmd.CommandText = '[dbo].[sp_APITest]'

    $pDate = $cmd.Parameters.Add('@date',[datetime])
    $pDate.ParameterDirection.Input 

    $pName = $cmd.Parameters.Add('@Name',[string])
    $pName.Parameterdirection.Input
     
    Invoke-RestMethod -Uri https://date.nager.at/api/v2/publicholidays/2020/US |
    Format-Table -Property Date, Name

    Thank you in advance for any assitance!

  • May 6, 2023 at 9:10 pm

    #4185379

    Thanks for posting your issue and hopefully someone will answer soon.

    This is an automated bump to increase visibility of your question.

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply