Being able to quickly submit data from PowerShell to SQL is something I often do. Usually I want the results inserted into a SQL table. Being able to do so in bulk without needing a for each insert loop is very helpful and much faster. The easiest way to do this is to use sqlclient.sqlbulkcopy. It requires that the input variable be a DataTable object so if it’s not already use the ConvertTo-DataTable function I wrote earlier. Once you have your data in the DataTable you can use it as the source in this Copy-DataTableToSQL function I wrote.
Click here to download Copy-DataTableToSQL
EXAMPLE:
Import-Module C:\PowerShell\ConvertTo-DataTable.ps1 Import-Module C:\PowerShell\Copy-DataTableToSQL.ps1 $Users = Get-ADUser -Server fitchaddc02 -Filter * #Using as example data $DataTable = ConvertTo-DataTable -Source $Users -NotMatch "write|propert|object|psshow" #Filtering junk columns from Get-ADUser that I don't want $DataTable.Columns.columnname #This lists the names of all columns $DataTable.rows[0] #Test to show one row $DataTable.rows.Count #Number of rows in DataTalbe, can compare to $Users.count Copy-DataTableToSQL -SQLServer fitchmsql01 -SQLDB DEV -Target dbo.adusers -Source $DataTable
CODE:
function Copy-DataTableToSQL { <# .EXAMPLE Copy-DataTableToSQL -SQLServer fitchmsql01 -SQLDB dev -Target dbo.test_adcomputer -Source $DataTable .PARAMETER Source A DataTable that will be merged witht eh target tabled. .PARAMETER SQLServer Named instances SERVERNAME\INSTANCENAME | If it's a default instance only use server name, do not add \MSSQLSERVER. .PARAMETER SQLDB Name of the database where the target table is. .PARAMETER Target The Table in which you're targeting .PARAMETER Filter Default="*" Used in selecting the columns from the target. .PARAMETER Security Default="SSPI" Used in connection string to SQL DB. .PARAMETER Timeout Default="60" Used in connection string. #> param( [Parameter(Mandatory=$true)][string]$SQLServer, [Parameter(Mandatory=$true)][string]$SQLDB, [Parameter(Mandatory=$true)][string]$Target, [Parameter(Mandatory=$true)][System.Data.DataTable]$Source, [String]$Filter="*", [String]$Security="SSPI", [Int]$Timeout="60", [Int]$Batch="5000" ) begin{ #Create connection object to SQL instance if one isnt already open if ($SQLConnection.State -ne [Data.ConnectionState]::Open) { $SQLConnection = New-Object System.Data.SqlClient.SqlConnection $SQLConnection.ConnectionString = "Server=$SQLServer;Database=$SQLDB;Integrated Security=$Security;Connection Timeout=$Timeout" $SQLConnection.Open() } } Process{ if ($SQLConnection.State -ne [Data.ConnectionState]::Open){throw "Connection to SQL DB not open"} else { $SQLBulkCopy = New-Object ("System.Data.SqlClient.SqlBulkCopy") $SQLConnection $SQLBulkCopy.DestinationTableName = $Target $SQLBulkCopy.BatchSize = $Batch $SQLBulkCopy.BulkCopyTimeout = $Timeout #Previously set to 0 foreach ($Column in $Source.columns.columnname){[void]$SQLBulkCopy.ColumnMappings.Add($Column, $Column)} $SQLBulkCopy.WriteToServer($Source) } } end{ if ($SQLConnection.State -eq [Data.ConnectionState]::Open){$SQLConnection.Close()} } }