There are better ways to do SQL inserts but sometimes you just want to generate SQL. Maybe? Either way, it shows the use of classes in PowerShell.
class BuildSQLArray { <# .SYNOPSIS Class to build SQL 'insert' value rows easily. .METHODS AddRow() GetRows() GenSQL() .PARAMETER AddRow([System.Collections.ArrayList]$Row) GenSQL([string]$Table, [System.Collections.ArrayList]$Columns) .EXAMPLE $SQLBuilder = New-Object BuildSQLArray [System.Collections.ArrayList]$ColValues = @("'a'","'b'","'c'","'d'") $SQLBuilder.AddRow($ColValues) [System.Collections.ArrayList]$ColValues = @("'e'","'f'","'g'","'h'") $SQLBuilder.AddRow($ColValues) [System.Collections.ArrayList]$ColValues = @("'i'","'j'","'k'","'l'") $SQLBuilder.AddRow($ColValues) [System.Collections.ArrayList]$ColValues = @("'m'","'n'","'o'","'p'") $SQLBuilder.AddRow($ColValues) ForEach ($List in $SQLBuilder.GetRows()) { Write-Host $List } $InsertSQL = $SQLBuilder.GenSQL("DBO.TheTable", @("1"; "2"; "3"; "4")) Write-Host $InsertSQL .RETURN [System.Collections.ArrayList]$Rows = GetRows() [String]$Sql = GenSQL() #> [System.Collections.ArrayList]$ValueList = @() [System.Collections.ArrayList]$ValueLists = @() [int]$LimitRows = 800 [int]$RowCount = 0 [void]AddRow([System.Collections.ArrayList]$Row) { $this.RowCount += 1 if ($this.RowCount -le $this.LimitRows) { $StrRow = $Row -Join "," $StrRow = "({0})" -f $StrRow $this.ValueList.Add($StrRow) } else { $this.UpdateRows() $StrRow = $Row -Join "," $StrRow = "({0})" -f $StrRow $this.ValueList.Add($StrRow) } } [void]UpdateRows() { if ($this.ValueList) { $StrRows = $this.ValueList -join "," $StrRows = "{0}" -f $StrRows $this.ValueLists += $StrRows [System.Collections.ArrayList]$this.ValueList = @() $this.RowCount = 0 } } [array]GenSQL([String]$Table, [Array]$Columns) { $SQL = @() $Vals = $this.GetRows() $Columns = $Columns -Join ',' if ($Vals) { ForEach ($Grp in $Vals) { $SQL += "INSERT INTO $Table ($Columns) VALUES $Grp;" } } return $SQL #-Join "`n" } [System.Collections.ArrayList]GetRows() { $this.UpdateRows() return $this.ValueLists } } <# MAIN: Test #> cls # Instantiate the class. $SQLBuilder = New-Object BuildSQLArray # Limit values per insert command. Best to keep this even, and I do not check for odd. $SQLBuilder.RowCount = 2 # Add some row values. [System.Collections.ArrayList]$ColValues = @("'a'","'b'","'c'","'d'") $SQLBuilder.AddRow($ColValues) [System.Collections.ArrayList]$ColValues = @("'e'","'f'","'g'","'h'") $SQLBuilder.AddRow($ColValues) [System.Collections.ArrayList]$ColValues = @("'i'","'j'","'k'","'l'") $SQLBuilder.AddRow($ColValues) [System.Collections.ArrayList]$ColValues = @("'m'","'n'","'o'","'p'") $SQLBuilder.AddRow($ColValues) # Add the table and columns. $InsertSQL = $SQLBuilder.GenSQL("DBO.TheTable", @("1"; "2"; "3"; "4")) # Check the result. ForEach ($SQL in $InsertSQL) { Write-Host $SQL }