compareWriteToDB.ps1

[CmdletBinding()]
param (
    [Parameter(Mandatory=$true)]
    [ValidateSet('DbaDBTableData','SqlCommand')]
    $Method,
    [Parameter(Mandatory=$true)]
    $Path,
    [Parameter(Mandatory=$true)]
    $ServerInstance,
    [Parameter(Mandatory=$true)]
    $Database,
    [Parameter(Mandatory=$true)]
    $Schema,
    [Parameter(Mandatory=$true)]
    $Table
)

$data = Get-ChildItem $Path -File -Recurse
Write-host "Object count: $($data.Count)"

if ($Method -eq "DbaDBTableData"){
    Measure-Command{
        foreach ($item in $data) {
            Write-DbaDbTableData -InputObject $item -SqlInstance $serverInstance -Database $Database -Schema $Schema -Table $Table
        }
    }
}

if ($Method -eq "SqlCommand"){
    Measure-Command{
        foreach ($item in $data) {
            [System.Collections.ArrayList]$itemKeys = @()
            [System.Collections.ArrayList]$itemValues = @()
            
            foreach ($property in ($item | get-member | Where-Object membertype -like "*property")) {
                $itemKeys.Add($property.name) | Out-Null
                if ($item.($property.name)) {
                    $itemValues.Add(($item.($property.name))) | Out-Null
                }else{
                    $itemValues.Add("") | Out-Null
                }					
            }

            $SqlParameters = @{
                ServerInstance = $ServerInstance
                Query = "
                INSERT INTO [$Database].[$Schema].[$Table] ($('"{0}"' -f ($itemKeys -join '","')))
                VALUES ($("'$($itemValues -join "','")'"));" 
            }

            Invoke-Sqlcmd @SqlParameters
        }
    }
}