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 )

Google+ photo

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

Connecting to %s

%d bloggers like this: