Created
January 1, 2016 01:32
-
-
Save potatoqualitee/7a8da1c8b31696df7695 to your computer and use it in GitHub Desktop.
sql-to-sql powershell bulkcopy import speedtest
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
# Setup runspace pool and the scriptblock that runs inside each runspace | |
$pool = [RunspaceFactory]::CreateRunspacePool(1,5) | |
$pool.Open() | |
$jobs = @() | |
# This is the workhorse. | |
$scriptblock = { | |
$source = "Data Source=sqlserver;Integrated Security=True;Connection Timeout=3;Initial Catalog=pssqlbulkcopy" | |
$dest = "Data Source=sql2016;Integrated Security=True;Connection Timeout=3;Initial Catalog=pssqlbulkcopy" | |
$conn = New-Object System.Data.SqlClient.SqlConnection | |
$conn.ConnectionString = $source | |
$conn.Open() | |
$cmd = New-Object System.Data.SqlClient.SqlCommand | |
$cmd.Connection = $conn | |
$sql = "SELECT * from [pssqlbulkcopy].[dbo].[speedtest] WITH (SNAPSHOT)" | |
$cmd.CommandText = $sql | |
$bulkcopy = New-Object Data.SqlClient.SqlBulkCopy($dest) | |
$bulkcopy.DestinationTableName = "speedtest" | |
$bulkcopy.BatchSize = 1000000 | |
$bulkcopy.WriteToServer($cmd.ExecuteReader()) | |
$bulkcopy.Close() | |
$conn.Close() | |
return $error[0] | |
} | |
$elapsed = [System.Diagnostics.Stopwatch]::StartNew() | |
1..10 | foreach { | |
$job = [PowerShell]::Create() | |
$null = $job.AddScript($scriptblock) | |
$job.RunspacePool = $pool | |
$jobs += [PSCustomObject]@{ Pipe = $job; Status = $job.BeginInvoke() } | |
} | |
while ($jobs.Status.IsCompleted -notcontains $true) {} | |
$secs = $elapsed.Elapsed | |
$errors = @() | |
foreach ($job in $jobs) { $results += $job.Pipe.EndInvoke($job.Status) ;$job.Pipe.Dispose() } | |
$elapsed | |
$pool.Close() |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment