Bulk Copy Data From PowerShell to SQL


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


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


function Copy-DataTableToSQL
 Copy-DataTableToSQL -SQLServer fitchmsql01 -SQLDB dev -Target dbo.test_adcomputer -Source $DataTable
 A DataTable that will be merged witht eh target tabled.
 Named instances SERVERNAME\INSTANCENAME | If it's a default instance only use server name, do not add \MSSQLSERVER.
 Name of the database where the target table is.
 The Table in which you're targeting
 Default="*" Used in selecting the columns from the target.
 .PARAMETER Security
 Default="SSPI" Used in connection string to SQL DB.
 Default="60" Used in connection string.
 #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"
 if ($SQLConnection.State -ne [Data.ConnectionState]::Open){throw "Connection to SQL DB not open"}
 $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)}
 if ($SQLConnection.State -eq [Data.ConnectionState]::Open){$SQLConnection.Close()}

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.