SQL Server: inserting 14000+ rows with Powershell

Question

I have written a SQL query to insert some server properties into a database. The problem is that I am now working with 14000+ servers and the INSERT command seems to have a 1000 row limit. I would like to contact the database as few times as possible, and stumbled on Write-SqlTableData, but I am getting an error:

Write-SqlTableData -DatabaseName $SQLDatabaseName -SchemaName dbo 
                   -TableName all.computer -Credential $DatabaseUserCredential 
                   -InputData $sqlValues -ServerInstance "$SQLServerName\MSSQLSERVER"

Write-SqlTableData : Failed to connect to server sqlsrv01.domain.local\MSSQLSERVER.

The code snippet is below:

#$allComputers = ...retrieved from API
$SqlServerName = 'sqlsrv01.domain.local'
$DatabaseUserCredential = Get-Credential

$sqlValues = New-Object Collections.ArrayList

$allComputers.computers | ForEach-Object {
    $_ | ForEach-Object {
        $_.PsObject.Properties | ForEach-Object {
            If ($_.Value -match "'") {
                $_.Value = $_.Value.Replace("'", "''")
            }
        }
    }

    $sqlValues.Add("('$($_.aa)', '$($_.phone_number)', '$($_.manual_scan_time)', '$($_.ip_addr)', '$($_.last_logon_user_guid)', '$($_.urlfilter_violated)', '$($_.last_connect_time)', '$($_.id)', '$($_.spyware_detected)', '$($_.arch)', '$($_.sched_start_time)', '$($_.virus_detected)', '$($_.platform)', '$($_.version)', '$($_.manual_aggressive_complete_time)', '$($_.online)', '$($_.type)', '$($_.status)', '$($_.spam_detected)', '$($_.sched_complete_time)', '$($_.sched_scan_time)', '$($_.pop3_scan)', '$($_.manual_aggressive_start_time)', '$($_.last_logon_user_account)', '$($_.name)', '$($_.ss_service)', '$($_.scan_mode)', '$($_.manual_complete_time)', '$($_.mac_addr)', '$($_.components)', '$($_.manual_start_time)', '$($_.last_logon_user_domain)', '$($_.created_at)', '$($_.last_connect_time_human)', '$($_.CustomerName)', '$(Get-Date)')")
    }

$SQLQuery = ("USE $SQLDatabaseName`r`n INSERT INTO [dbo].[all.computer] (aa, phone_number, manual_scan_time, ip_addr, last_logon_user_guid, urlfilter_violated, last_connect_time, id, spyware_detected, arch, sched_start_time, virus_detected, platform, version, manual_aggressive_complete_time, online, type, status, spam_detected, sched_complete_time, sched_scan_time, pop3_scan, manual_aggressive_start_time, last_logon_user_account, name, ss_service, scan_mode, manual_complete_time, mac_addr, components, manual_start_time, last_logon_user_domain, created_at, last_connect_time_human, CustomerName, QueryDate)`r`nValues {0}" -f ($sqlValues -join "`r`n,"))

Invoke-Sqlcmd -Query $SQLQuery -ServerInstance $SQLServerName -Credential $DatabaseUserCredential -ErrorAction Stop

The $allComputers object is an array of objects that look like this:

aa                              : True
phone_number                    :
manual_scan_time                : 0
ip_addr                         : 10.50.0.147
last_logon_user_guid            : 286c1410eb470e4ea31dd17e9c1eee31
urlfilter_violated              : 0
last_connect_time               : 1563379779
id                              : 0542c82cada09040be79bfb6e54fd119
spyware_detected                : 0
arch                            : x64
sched_start_time                : 1562922019
virus_detected                  : 0
platform                        : Win Server 2012 R2
version                         : 6.6.2457/14.1.1516
manual_aggressive_complete_time : 0
online                          : True
type                            : 1
status                          : 1
spam_detected                   : 0
sched_complete_time             : 1562926028
sched_scan_time                 : 1559294629
pop3_scan                       : False
manual_aggressive_start_time    : 0
last_logon_user_account         : jdoe
name                            : server01
ss_service                      : True
scan_mode                       : 0
manual_complete_time            : 0
mac_addr                        : 2B:41:38:B4:34:46
components                      : {@{version=0; type=1}, @{version=11.000.1006; type=3}, @{version=15.239.00; type=14}}
manual_start_time               : 0
last_logon_user_domain          : acme
created_at                      : 1510519578
last_connect_time_human         : 7/17/2019 4:09:39 PM
CustomerName                    : Acme Corp

The questions are:

Am I on the right track or is there a better way to insert those rows into the database with one call?
Any idea why I cannot connect? Write-SqlTableData seems to require the instance name, but I am connecting fine without it, when I use Invoke-Sqlcmd.

1000 limit could be mitigated: https://stackoverflow.com/a/42703601/5070879 — Lukasz Szozda, Jul 17 '19 at 17:04

score 2 · Accepted Answer · answered Jul 17 '19 at 18:08

I would say the "Recommended" way is to split your batches up into batches of 1,000. Even though you could do everything in one go, and there are workarounds, I wouldn't do it as you will likely eventually run into other issues (e.g. memory and query size limitations) especially because the data you are inserting is quite "large" per-row.

To do that, simply use a for loop:

for($i=0; $i -lt $sqlValues.Count; $i+=1000){
    Write-Host "Loop: $i"
    $SQLQuery = ("USE $SQLDatabaseName`r`n INSERT INTO [dbo].[all.computer] (aa, phone_number, manual_scan_time, ip_addr, last_logon_user_guid, urlfilter_violated, last_connect_time, id, spyware_detected, arch, sched_start_time, virus_detected, platform, version, manual_aggressive_complete_time, online, type, status, spam_detected, sched_complete_time, sched_scan_time, pop3_scan, manual_aggressive_start_time, last_logon_user_account, name, ss_service, scan_mode, manual_complete_time, mac_addr, components, manual_start_time, last_logon_user_domain, created_at, last_connect_time_human, CustomerName, QueryDate)`r`nValues {0}" -f ($sqlValues[$i..($i+999)] -join  "`r`n,"))

    Invoke-Sqlcmd -Query $SQLQuery -ServerInstance $SQLServerName -Credential $DatabaseUserCredential -ErrorAction Stop
}

Where the magic is selecting 1000 row batches with:

$sqlValues[$i..($i+999)]

PowerShell is a good hammer for problems like this. An alternative way that is more suited to tasks like this is to use SQL Server Integration Services (SSIS) which are designed to ingest huge amounts of disparate data (including API's) and "Extract Transform Load" (ETL) them into databases with high performance.

SQL Server: inserting 14000+ rows with Powershell

1 Answers1