public/Invoke-BulkCopy.ps1

  
  
<#
      .SYNOPSIS
          Uses the .NET SQLBulkCopy class to quickly copy rows into a destination table.
  
      .DESCRIPTION
          
          Also, the invoke-bulkcopy function allows you to pass a command object instead of a set of records in order to "stream" the records
          into the destination in cases where there are a lot of records and you don't want to allocate memory to hold the entire result set.
  
      .PARAMETER records
          Either a datatable (like one returned from invoke-query or invoke-storedprocedure) or
          A command object (e.g. new-sqlcommand), or a datareader object. Note that the command object or datareader object
          can come from any class that inherits from System.Data.Common.DbCommand or System.Data.Common.DataReader, so this will work
          with most ADO.NET client libraries (not just SQL Server).
  
      .PARAMETER Server
          The destination server to connect to.
  
      .PARAMETER Database
          The initial database for the connection.
  
      .PARAMETER User
          The sql user to use for the connection. If user is not passed, NT Authentication is used.
  
      .PARAMETER Password
          The password for the sql user named by the User parameter.
  
      .PARAMETER Table
          The destination table for the bulk copy operation.
  
      .PARAMETER Mapping
          A dictionary of column mappings of the form DestColumn=SourceColumn
  
      .PARAMETER BatchSize
          The batch size for the bulk copy operation.
  
      .PARAMETER Transaction
          A transaction to execute the bulk copy operation in.
  
      .PARAMETER NotifyAfter
          The number of rows to fire the notification event after transferring. 0 means don't notify.
          Ex: 1000 means to fire the notify event after each 1000 rows are transferred.
          
      .PARAMETER NotifyFunction
          A scriptblock to be executed after each $notifyAfter records has been copied. The second parameter ($param[1])
          is a SqlRowsCopiedEventArgs object, which has a RowsCopied property. The default value for this parameter echoes the
          number of rows copied to the console
          
      .PARAMETER Options
          An object containing special options to modify the bulk copy operation.
          See http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlbulkcopyoptions.aspx for values.
  
      .PARAMETER GenerateMapping
         Switch indicating whether to generate mapping (direct column-to-column). If you want a custom mapping, use the Mapping parameter.
      .EXAMPLE
          PS C:\> $cmd=new-sqlcommand -server MyServer -sql "Select * from MyTable"
          PS C:\> invoke-sqlbulkcopy -records $cmd -server MyOtherServer -table CopyOfMyTable
  
      
      .INPUTS
          None.
          You cannot pipe objects to invoke-bulkcopy
  
      .OUTPUTS
          System.Data.SqlClient.SqlCommand
  
  #>

function Invoke-Bulkcopy {
    param([Parameter(Mandatory = $true)]$records,
        [Parameter(Mandatory = $true)]$server,
        [string]$database,
        [string]$user,
        [string]$password,
        [Parameter(Mandatory = $true)][string]$table,
        [hashtable]$mapping = @{},
        [int]$batchsize = 0,
        [System.Data.SqlClient.SqlTransaction]$transaction = $null,
        [int]$notifyAfter = 0,
        [scriptblock]$notifyFunction = { Write-Host "$($args[1].RowsCopied) rows copied." },
        [System.Data.SqlClient.SqlBulkCopyOptions]$options = [System.Data.SqlClient.SqlBulkCopyOptions]::Default,
        [Switch]$GenerateMapping)
  
    #use existing "New-Connection" function to create a connection string.
    $connection = New-Connection -server $server -database $Database -User $user -password $password
    $connectionString = $connection.ConnectionString
    $connection.close()
  
    #Use a transaction if one was specified
    if ($transaction -is [System.Data.SqlClient.SqlTransaction]) {
        $bulkCopy = new-object "Data.SqlClient.SqlBulkCopy" $connectionString $options  $transaction
    }
    else {
        $bulkCopy = new-object "Data.SqlClient.SqlBulkCopy" $connectionString
    }
    $bulkCopy.BatchSize = $batchSize
    $bulkCopy.DestinationTableName = $table
    $bulkCopy.BulkCopyTimeout = 10000000
    if ($notifyAfter -gt 0) {
        $bulkCopy.NotifyAfter = $notifyafter
        $bulkCopy.Add_SQlRowscopied($notifyFunction)
    }
    if ($psboundParameters.ContainsKey('mapping')) {
        #Add column mappings if they were supplied
        foreach ($key in $mapping.Keys) {
            $bulkCopy.ColumnMappings.Add($mapping[$key], $key) | out-null
        }
    }
    elseif ($GenerateMapping) {
        $columns = $records.Rows[0].table.columns.columnname
        foreach ($column in $columns) {
            $bulkCopy.ColumnMappings.Add($column, $column)| out-null
        }
    }
      
    write-debug "Bulk copy starting at $(get-date)"
    if ($records -is [System.Data.Common.DBCommand]) {
        #if passed a command object (rather than a datatable), ask it for a datareader to stream the records
        $bulkCopy.WriteToServer($records.ExecuteReader())
    }
    elseif ($records -is [System.Data.Common.DbDataReader]) {
        #if passed a Datareader object use it to stream the records
        $bulkCopy.WriteToServer($records)
    }
    else {
        $bulkCopy.WriteToServer($records)
    }
    write-debug "Bulk copy finished at $(get-date)"
}