Invoke SqlBulkCopy

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.

Returns number of rows copied.

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.

Parameters

Group = ParameterSetName; Mandatory = YES|NO|<default value>

Name	Type	Mandatory	Group	Description
SourceConnectionName	String	"default"		User defined name for connection where data will be queried from.
DestinationConnectionName	String	"default"		User defined name for connection where data will be inserted to.
SourceTable	String	YES	table	The name of the table in the source connection.
SourceQuery	String[]	YES	query	The query to determine the source data, instead of specifying a table.
SourceParameters	Hashtable	NO	query	Parameters needed for the source query.
DestinationTable	String	NO	query	The name of the table to write to in the destination connection. If not specified, will be taken from SourceTable parameter.
DestinationTable	String	YES	table	The name of the table to write to in the destination connection. If not specified, will be taken from SourceTable parameter.
ColumnMap	Hashtable	NO		Key is the column name in the source connection. Value is the column name in the destination connection.
BatchSize	Integer	500		How many inserts are batched together at one time.
BatchTimeout	Integer	-1		How long, in seconds, that each batch can take. Defaults (-1) to the command timeout for the source connection.
Notify	Switch			If present, as each batch completes a progress notification will be generated with the total number of rows inserted so far.
NotifyAction	Scriptblock			If specified, then on the completion of each batch, this action will be invoked. The first argument will have the rows completed so far, either use $args[0] or specify a param block.