Upserting Data to Azure SQL DB using PowerShell

For a project involving Azure Security I needed to store fairly large amounts of data in an Azure PaaS database using PowerShell.

If a row already exists, I want to do an UPDATE command, otherwise an INSERT command, also known as an UPSERT in SQL.

It should also use parameters to avoid issues with quotes in fields, and should convert PowerShell null’s/empty objects to the SQL equivalent.

The following function is what resulted, it only supports a single WHERE clause, but should be easy to enhance for those looking to improve 🙂

function invoke-sqlUpsert{
    Param(
        [String]$tableName,
        [Array]$values, # example: @(@{"column"="deviceId";"value"="123415";"dataType"=[Data.SQLDBType]::NVarChar})
        [PSObject]$primaryKey, # example: @{"column"="deviceId";"value"="123415";"dataType"=[Data.SQLDBType]::NVarChar}
        [System.Data.Common.DbConnection]$sqlConn
    )

    $sqlQuery = "BEGIN TRANSACTION;
    UPDATE $tableName
        SET "
    for($i = 0;$i -lt $values.Count;$i++){
        $sqlQuery = "$sqlQuery$($values[$i].column)=@$($values[$i].column)"
        if($i -lt $values.Count-1){
            $sqlQuery = "$sqlQuery,"
        }
    }
    $sqlQuery = "$($sqlQuery)
        WHERE $($primaryKey.column) = @$($primaryKey.column);
    IF @@ROWCOUNT = 0
        BEGIN
            INSERT INTO $tableName ($($primaryKey.column),"
    for($i = 0;$i -lt $values.Count;$i++){
        $sqlQuery = "$sqlQuery$($values[$i].column)"
        if($i -lt $values.Count-1){
            $sqlQuery = "$sqlQuery,"
        }
    }
    $sqlQuery = "$($sqlQuery)
            )
            VALUES (@$($primaryKey.column),"
    for($i = 0;$i -lt $values.Count;$i++){
        $sqlQuery = "$($sqlQuery)@$($values[$i].column)"
        if($i -lt $values.Count-1){
            $sqlQuery = "$sqlQuery,"
        }
    }
    $sqlQuery = "$($sqlQuery));
        END
    COMMIT TRANSACTION;"
           

    $sqlCmd=new-object system.Data.SqlClient.SqlCommand($sqlQuery, $sqlConn)
    $sqlCmd.Parameters.Add((New-OBJECT DATA.SQLClient.SQLParameter("@$($primaryKey.column)",$primaryKey.dataType))) | OUT-NULL
    $sqlCmd.Parameters[0].Value = $primaryKey.value
    for($i = 0;$i -lt $values.Count;$i++){
        $sqlCmd.Parameters.Add((New-OBJECT DATA.SQLClient.SQLParameter("@$($values[$i].column)",$values[$i].dataType))) | OUT-NULL
        $sqlCmd.Parameters[$i+1].Value = $values[$i].value
    }

    for($i=0;$i -lt $sqlCmd.Parameters.count;$i++){
        if($sqlCmd.Parameters[$i].Value -eq $null){
            $sqlCmd.Parameters[$i].Value = [System.DBNull]::Value
        }
    }
    if($sqlCmd.ExecuteNonQuery() -ne 1){
        Throw $_
    }
}

An example of how to connect from an Azure Function before using this command:

using namespace System.Data.SqlClient
using namespace System.Net
$msi_authenticationResult = Invoke-RestMethod -Method Get -Headers @{'Secret' = $env:MSI_SECRET} -Uri ($env:MSI_ENDPOINT +'?resource=https://database.windows.net/&api-version=2017-09-01')
$sqlConn = New-Object System.Data.SqlClient.SqlConnection
$sqlConn.ConnectionString = "Data Source = yourserver.database.windows.net; Initial Catalog = yourdatabase"
$sqlConn.AccessToken = $msi_authenticationResult.access_token
$sqlConn.Open()

Liebensraum

Upserting Data to Azure SQL DB using PowerShell

Microsoft 365, Azure, Automation & Code