Synopsis

Invokes a bulk copy operation.

Syntax

Invoke-SqlBulkCopy [-SourceConnectionName <String>] [-DestinationConnectionName <String>] -SourceTable <String> [-DestinationTable <String>] [-ColumnMap <Hashtable>] [-BatchSize <Int32>] [-BatchTimeout <Int32>] [-Notify] [<CommonParameters>]

Invoke-SqlBulkCopy [-SourceConnectionName <String>] [-DestinationConnectionName <String>] -SourceQuery <String> [-SourceParameters <Hashtable>] -DestinationTable <String> [-ColumnMap <Hashtable>] [-BatchSize <Int32>] [-BatchTimeout <Int32>] [-Notify] [<CommonParameters>]

Description

Invokes a bulk copy operation. This is highly optimized for MS SQL as the destination (using the .net SQLBulkCopy class). This is highly optimized for SQLite (wrapping inserts in transactions, per documentation). This is optimized for PostGre and Oracle (wrapping inserts in transactions) but not as efficient as vendor specific implementations of bulk inserts.

If you don’t specify a SourceConnectionName or DestinationConnectionName, they will use the default (the cmdlet will fail is you don’t specify at least one).
If you don’t specify DestinationTable, it will use SourceTable. 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.

Parameters

-SourceConnectionName <String>

User defined name for connection where data will be returned from.

Required? False
Position? named
Default value Default
Accept pipeline input? false
Accept wildcard characters? False

-DestinationConnectionName <String>

User defined name for connection where data will be written to.

Required? False
Position? named
Default value Default
Accept pipeline input? false
Accept wildcard characters? False

-SourceTable <String>

The name of the table in the source connection.

Required? True
Position? named
Default value  
Accept pipeline input? false
Accept wildcard characters? False

-DestinationTable <String>

The name of the table to write to in the destination connection. If not specified, will be taken from SourceTable parameter.

Required? False
Position? named
Default value  
Accept pipeline input? false
Accept wildcard characters? False

-ColumnMap <Hashtable>

Key is the column name in the source connection, Value is the column name in the destination connection.

Required? False
Position? named
Default value  
Accept pipeline input? false
Accept wildcard characters? False

-BatchSize <Int32>

How many inserts are batched together at one time.

Required? False
Position? named
Default value 100
Accept pipeline input? false
Accept wildcard characters? False

-BatchTimeout <Int32>

How long, in seconds, that each batch can take. Defaults to the command timeout for the destination connection.

Required? False
Position? named
Default value 0
Accept pipeline input? false
Accept wildcard characters? False

-Notify <SwitchParameter>

If present, as each batch completes a progress notification will be generated with the total number of rows inserted so far.

Required? False
Position? named
Default value  
Accept pipeline input? false
Accept wildcard characters? False

-SourceQuery <String>

A query to determine the source data, instead of a table.

Required? True
Position? named
Default value  
Accept pipeline input? false
Accept wildcard characters? False

-SourceParameters <Hashtable>

Parameters needed for the source query.

Required? False
Position? named
Default value  
Accept pipeline input? false
Accept wildcard characters? False

<CommonParameters>

This cmdlet supports the common parameters: Verbose, Debug,
ErrorAction, ErrorVariable, WarningAction, WarningVariable,
OutBuffer, PipelineVariable, and OutVariable. For more information, see
about_CommonParameters (http://go.microsoft.com/fwlink/?LinkID=113216).

Examples

Example 1

PS C:\> Invoke-SqlBulkCopy -SourceConnectionName src -DestinationConnectionName dst -SourceTable testTable

Will copy rows from ‘src’.testTable into ‘dst’.testTable.

Example 2

PS C:\> Invoke-SqlBulkCopy -SourceConnectionName src -DestinationConnectionName dst -SourceTable testTable -DestinationTable table2 -ColumnMap @{Col1="A";Col2="B";DT="theDate"}

Will copy rows from ‘src’.testTable into ‘dst’.table2; only the columns Col1, Col2 and DT from the src will be copied into A, B, theDate in dst (respectively).

Example 3

PS C:\> Invoke-SqlBulkCopy -SourceConnectionName src -DestinationConnectionName dst -SourceQuery "SELECT First, Last, BirthDate FROM employee WHERE status=@status" -SourceParameters @{status="dead"} -DestinationTable "deadEmployees" -ColumnMap @{First="fName";Last="lName";BirthDate="DOB"} -Notify -BatchSize 500 -BatchTimeout 180

Will copy rows from ‘src’.employee table (based upon the query) into ‘dst’.deadEmployees using the mapping (First = fname; Last = lName; BirthDate = DOB). Inserts will be batched into groups of 500, with an allowance of 3 minutes (180 seconds) to complete each batch. Progress notifications will fire after every batch completes.

Last edited Jun 23, 2015 at 2:30 PM by mastertook, version 3