PowerShell Script to get data from Third Party API and insert into database

Question

I have created a PowerShell script that will call a REST API and then for each node returned, add the data into a database table. This particular endpoint only returns ~600 records worth of data, however this is the method I will be using for a bunch of other API endpoints.

There are a couple things I think would help with this script:

• Bulk add the records into the database rather than one by one
• For the ON DUPLICATE KEY UPDATE only change fields that are different.

My understanding of ON DUPLICATE KEY UPDATE it will check if the ScryId exists, and if it does then update the fields listed below. I would like to only update the fields that are different.

Script:

If ( Get-Module -ListAvailable -Name SimplySQL ) { Import-Module -Name SimplySQL } Else { Install-Module -Name SimplySQL -Force }

Write-Host "---------------------------------------------------------------------------" -ForegroundColor DarkGray
Write-Host "Update Production.Set' Table: " -ForegroundColor Gray -NoNewline
$StartTime = $(Get-Date)

$Response = Invoke-RestMethod -Uri "https://api.scryfall.com/sets" -Method GET -UseBasicParsing
$ResponseSorted = $Response.data | Sort-Object -Property @{Expression = "released_at"; Descending = $False}

$username = ""
$password = ConvertTo-SecureString "" -AsPlainText -Force
$credential = New-Object System.Management.Automation.PSCredential ($username, $password)

Open-MySQLConnection -Server "" -Port 1234 -Database "Production" -Credential $credential

Foreach ( $Record in $ResponseSorted ) {
    $ScryID = $Record.id
    $Code = Try{ ($Record.code).ToUpper() } Catch {}
    $Name = Try{ ($Record.name) -replace "'","" } Catch {}
    $SetType = $Record.set_type
    $ReleaseDate = $Record.released_at
    $CardCount = $Record.card_count
    $BlockCode = Try{ ($Record.block_code).ToUpper() } Catch {}
    $BlockName = $Record.block
    $ParentSetCode = Try{ ($Record.parent_set_code).ToUpper() } Catch {}
    $Digital = $Record.digital
    $NonFoil = $Record.nonfoil_only
    $Foil = $Record.foil_only
    $Icon = $Record.icon_svg_uri
    $Status = 0

    Invoke-SQLUpdate -query "
        INSERT INTO Production.`Set`
            (ScryID, Code, Name, SetType, ReleaseDate, CardCount, BlockCode, BlockName, ParentSetCode, Digital, NonFoil, Foil, Icon, Status)
        VALUES
            ( '$ScryID', '$Code', '$Name', '$SetType', '$ReleaseDate', $CardCount, '$BlockCode', '$BlockName', '$ParentSetCode', $Digital, $NonFoil, $Foil, '$Icon', $Status )
        ON DUPLICATE KEY UPDATE
            Code = '$Code',
            Name = '$Name',
            SetType =  '$SetType',
            ReleaseDate = '$ReleaseDate',
            CardCount = $CardCount,
            BlockCode = '$BlockCode',
            BlockName = '$BlockName',
            ParentSetCode = '$ParentSetCode',
            Digital = $Digital,
            NonFoil = $NonFoil,
            Foil = $Foil,
            Icon = '$Icon'
    " | Out-Null
}

Close-SQLConnection

$ElapsedTime = $(Get-Date) - $StartTime
$TotalTime = "{0:HH:mm:ss.fff}" -f ([datetime]$ElapsedTime.Ticks)
Write-Host $TotalTime -ForegroundColor Cyan