BCP via Powershell

Quick post on how to use BCP with powershell.  Adjust options to suit your needs.

Import-Module SqlPS -DisableNameChecking

$query = "SELECT * FROM TABLE"


 $srcConn = New-Object System.Data.SqlClient.SqlConnection("PersistSecurityInfo = true;Server=Server1;Database=DBName;Integrated Security=True;Application Name=PowerShell_BCP");
 $destConn = New-Object System.Data.SqlClient.SqlConnection("PersistSecurityInfo = true;Server=Server2;Database=DBName;Integrated Security=True;Application Name=PowerShell_BCP");

 $srcCommand = New-Object System.Data.SqlClient.SqlCommand($query, $srcConn);
 $destBulk = New-Object System.Data.SqlClient.SqlBulkCopy($destConn);
 $destBulk.BulkCopyTimeout = 0;
 $destBulk.BatchSize = 1000
 $destBulk.DestinationTableName = 'DestTable'


 $results = $srcCommand.ExecuteReader();


 $_ | fl -Force

Leave a Reply

Fill in your details below or click an icon to log in:

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.