PowerShell - Write array of objects to a SQL table

This script srite array of objects to a SQL table. The input is via the pipeline, so usage could be

Get-Process | .\write-sqltable.ps1 -sqlserver SQLServer -dbname Mydatabase -dbtable Mytable

Note: the table MUST already exist with the same column names as the properties of the objects being passed to it. Use the -purgetable switch if you need to empty the table each time the script is run.


Script: write-sqltable.ps1

# Script to write array of objects, via Pipeline into a SQL table.
# Property names of objects being passed must match the column names in the destination table
# Returns select failedRowCount , SuccessRowCount , Errors 
# Params
# -sqlserver : Name or alias of SQL server instance
# -dbname : Name of the database
# -dbtable : Name of the database table
# -purgetable : [switch] Empty the table before writing back to it.

param ([string]$sqlServer = $(throw "SQL Server required"), [string]$dbName = $(throw "Database Required"),[string]$dbTable = $(throw "Database table Required"), [switch]$purgetable)
begin 
{
    $io = New-Object System.Collections.ArrayList;
}

process
{
    if ($_) {$io += $_}
}

end
{
    
    $conn = New-Object System.Data.SqlClient.SqlConnection("Data Source=$sqlServer;Initial Catalog=$dbName; Integrated Security=SSPI");
    $conn.Open();
    $cmd = $conn.CreateCommand();

    if ($purgetable){
        $cmd.CommandText = "DELETE from $dbtable"
        $cmd.ExecuteNonQuery() | out-null
        
    }
    if ($io -ne $null){
        $props = $io | select -First 1 | Get-Member -Membertype NoteProperty | foreach {$_.name}
        $successRowCount = 0
        $failedRowCount = 0
        $errors = @()
        foreach ($obj in $io)
        {
            $error.clear()
            $dbrowResult = $null
            $SQLInto = $null
            $SQLValues = $null
            $props | foreach {$SQLInto += $_ + ", "; $SQLValues += "'$($obj.$_)', "}
            $SQLInto = $SQLInto.trimEnd(", ")
            $SQLValues = $SQLValues.TrimEnd(", ")
            write-host "INSERT INTO $dbTable ($SQLInto) VALUES ($SQLValues)"
            $cmd.CommandText = "INSERT INTO $dbTable ($SQLInto) VALUES ($SQLValues)";
            try{$dbrowResult = $cmd.ExecuteNonQuery()}catch{}
            if ($dbrowResult -eq $null){
                $failedRowCount ++
                $errors += $error
                
            }
            else {
                $successRowCount ++
            }


        }
        
        $results = "" | select failedRowCount , SuccessRowCount , Errors 
        $results.failedRowCount = $failedRowCount
        $results.SuccessRowCount = $SuccessRowCount
        $flaterrors = ""
        $errors | Sort-Object -Unique | foreach {$results.Errors += ("{0}," -f $_)} | out-null; try{$results.Errors = ($results.Errors).TrimEnd(",")}catch{}
        $results
    }
    $conn.close();
}