Cmdlets/Invoke-SqlBulkCopy.ps1

<#
.Synopsis
    Executes a bulk copy between two connections.
 
.Description
    Executes a bulk copy operation between two connections. This is highly
    optimized if the destination has a managed bulkcopy implemenation, otherwise
    it is only generally optimized. For example, SQL Server has a bulk copy
    class (SqlBulkCopy) that is easily implemented and provides an efficient
    means of inserting data into SQL Server.
 
    The default implemenation, if the provider does not provider a managed
    bulk copy mechanism is to prepare the sql insert, and wrap multiple inserts
    into a single transaction (batching). This provides a significant
    performance improvement over looping with Invoke-SqlUpdate.
 
    CONSIDERATIONS
    * You must specify either a SourceConnectionName or DestinationConnectionName,
        whichever one is not specified will use 'default', not specifying either
        will cause an error.
    * If you don’t specify DestinationTable, it will use SourceTable; however
        DestinationTable is required if you use SourceQuery.
    * If you specify ColumnMap and Source Table, then the select against the
        SourceConnection will be limited to the columns you specified in ColumnMap.
 
    Returns number of rows copied.
 
.Parameter SourceConnectionName
    User defined name for connection where data will be queried from.
 
.Parameter DestinationConnectionName
    User defined name for connection where data will be inserted to.
 
.Parameter SourceTable
    The name of the table in the source connection.
 
.Parameter DestinationTable
    The name of the table to write to in the destination connection.
    If not specified, will be taken from SourceTable parameter.
 
.Parameter ColumnMap
    Key is the column name in the source connection.
    Value is the column name in the destination connection.
 
.Parameter BatchSize
    How many inserts are batched together at one time.
 
.Parameter BatchTimeout
    How long, in seconds, that each batch can take.
    Defaults to the command timeout for the source connection.
 
.Parameter Notify
    If present, as each batch completes a progress notification will be
    generated with the total number of rows inserted so far.
 
.Parameter SourceQuery
    The query to determine the source data, instead of specifying a table.
 
.Parameter SourceParameters
    Parameters needed for the source query.
 
#>

Function Invoke-SqlBulkCopy {
    [CmdletBinding()]
    Param(
        [string]$SourceConnectionName = "default"
        , [string]$DestinationConnectionName = "default"
        , [Parameter(Mandatory, ParameterSetName="table")][string]$SourceTable
        , [Parameter(Mandatory, ParameterSetName="query")][AllowEmptyString()][string[]]$SourceQuery
        , [Parameter(ParameterSetName="query")][hashtable]$SourceParameters = @{}
        , [Parameter(ParameterSetName="table")]
            [Parameter(Mandatory, ParameterSetName="query")]
            [string]$DestinationTable
        , [hashtable]$ColumnMap = @{}
        , [ValidateRange(1,5000)][int]$BatchSize = 500
        , [int]$BatchTimeout = -1
        , [switch]$Notify
    )
    
    If($SourceConnectionName -eq $DestinationConnectionName) {
        $PSCmdlet.ThrowTerminatingError(
            [System.Management.Automation.ErrorRecord]::new(
                [System.Management.Automation.PSArgumentException]::new(
                    "You cannot use the same connection for both the source and destination!", "SourceConnectionName, DestinationConnectionName"
                ), $null, [System.Management.Automation.ErrorCategory]::InvalidArgument, $null
            )
        )
    }

    If((TestConnectionName -ConnectionName $SourceConnectionName) -and (TestConnectionName -ConnectionName $DestinationConnectionName)) {
        If($BatchTimeout -lt 0) { $BatchTimeout = $script:Connections.$SourceConnectionName.CommandTimeout }
        Try {
            If($PSCmdlet.ParameterSetName -eq "table") {
                If([string]::IsNullOrWhiteSpace($DestinationTable)) { $DestinationTable -eq $SourceTable }
                If($ColumnMap -and $ColumnMap.Count -gt 0) { $SourceQuery = "SELECT {0} FROM $SourceTable" -f $ColumnMap.Keys -join ", " }
                Else { $SourceQuery = "SELECT * FROM $SourceTable" }
            }
            Else { [string]$SourceQuery = $SourceQuery -join [System.Environment]::NewLine }
            
            If(-not $SourceParameters) { $SourceParameters = @{} }
            $srcReader = $script:Connections.$SourceConnectionName.GetReader($SourceQuery, $BatchTimeout, $SourceParameters)
            If($Notify.IsPresent) {
                $script:Connections.$DestinationConnectionName.BulkLoad($srcReader, $DestinationTable, $ColumnMap, $BatchSize, $BatchTimeout, {
                        Param([int]$insertCount)
                        Write-Progress -Activity "SimplySql BulkCopy" -Status $DestinationTable -CurrentOperation "Inserted $insertCount rows."
                    }.GetNewClosure())
                Write-Progress -Activity "SimplySql BulkCopy" -Status "Finished" -Completed
            }
            Else { $script:Connections.$DestinationConnectionName.BulkLoad($srcReader, $DestinationTable, $ColumnMap, $BatchSize, $BatchTimeout, $null) }
        }
        Finally {
            If(Test-Path variable:srcReader) { $srcReader.Dispose() }
        }
    }
    Else { Throw [System.Management.Automation.PSArgumentException]::new("Invalid Connection Name(s).", "SourceConnectionName, DestinationConnectionName") }
}

Export-ModuleMember -Function Invoke-SqlBulkCopy