| external help file | Module Name | online version | schema |
|---|---|---|---|
SimplySql.Cmdlets.dll-Help.xml |
SimplySql |
2.0.0 |
Executes a bulk copy between two connections.
Invoke-SqlBulkCopy [-SourceConnectionName <String>] [-DestinationConnectionName <String>]
[-DestinationTable <String>] -SourceTable <String> [-ColumnMap <Hashtable>] [-BatchSize <Int32>]
[-BatchTimeout <Int32>] [-Notify] [-NotifyAction <System.Action`1[System.Int64]>] [-WhatIf] [-Confirm]
[<CommonParameters>]
Invoke-SqlBulkCopy [-SourceConnectionName <String>] [-DestinationConnectionName <String>]
-DestinationTable <String> -SourceQuery <String[]> [-SourceParameters <Hashtable>] [-ColumnMap <Hashtable>]
[-BatchSize <Int32>] [-BatchTimeout <Int32>] [-Notify] [-NotifyAction <System.Action`1[System.Int64]>]
[-WhatIf] [-Confirm] [<CommonParameters>]
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.
How many inserts are batched together at one time.
Type: Int32
Parameter Sets: (All)
Aliases:
Required: False
Position: Named
Default value: 500
Accept pipeline input: False
Accept wildcard characters: FalseHow long, in seconds, that each batch can take. Defaults to the command timeout for the source connection.
Type: Int32
Parameter Sets: (All)
Aliases:
Required: False
Position: Named
Default value: None
Accept pipeline input: False
Accept wildcard characters: FalseKey is the column name in the source connection. Value is the column name in the destination connection.
Type: Hashtable
Parameter Sets: (All)
Aliases:
Required: False
Position: Named
Default value: None
Accept pipeline input: False
Accept wildcard characters: FalseUser defined name for connection where data will be inserted to.
Type: String
Parameter Sets: (All)
Aliases: DstCN
Required: False
Position: Named
Default value: default
Accept pipeline input: True (ByPropertyName)
Accept wildcard characters: FalseThe name of the table to write to in the destination connection. If not specified, will be taken from SourceTable parameter.
Type: String
Parameter Sets: table
Aliases:
Required: False
Position: Named
Default value: None
Accept pipeline input: True (ByPropertyName)
Accept wildcard characters: FalseType: String
Parameter Sets: query
Aliases:
Required: True
Position: Named
Default value: None
Accept pipeline input: True (ByPropertyName)
Accept wildcard characters: FalseIf present, as each batch completes a progress notification will be generated with the total number of rows inserted so far.
Type: SwitchParameter
Parameter Sets: (All)
Aliases:
Required: False
Position: Named
Default value: None
Accept pipeline input: False
Accept wildcard characters: FalseProvide a scriptblock to be executed after every BatchSize number of rows are inserted. Scriptblock will be called with the number of rows inserted so far.
Type: System.Action`1[System.Int64]
Parameter Sets: (All)
Aliases:
Required: False
Position: Named
Default value: None
Accept pipeline input: False
Accept wildcard characters: FalseUser defined name for connection where data will be queried from.
Type: String
Parameter Sets: (All)
Aliases: SrcCN
Required: False
Position: Named
Default value: default
Accept pipeline input: True (ByPropertyName)
Accept wildcard characters: FalseParameters needed for the source query.
Type: Hashtable
Parameter Sets: query
Aliases:
Required: False
Position: Named
Default value: None
Accept pipeline input: True (ByPropertyName)
Accept wildcard characters: FalseThe query to determine the source data, instead of specifying a table.
Type: String[]
Parameter Sets: query
Aliases:
Required: True
Position: Named
Default value: None
Accept pipeline input: True (ByPropertyName)
Accept wildcard characters: FalseThe name of the table in the source connection.
Type: String
Parameter Sets: table
Aliases:
Required: True
Position: Named
Default value: None
Accept pipeline input: True (ByPropertyName)
Accept wildcard characters: FalsePrompts you for confirmation before running the cmdlet.
Type: SwitchParameter
Parameter Sets: (All)
Aliases: cf
Required: False
Position: Named
Default value: None
Accept pipeline input: False
Accept wildcard characters: FalseShows what would happen if the cmdlet runs. The cmdlet is not run.
Type: SwitchParameter
Parameter Sets: (All)
Aliases: wi
Required: False
Position: Named
Default value: None
Accept pipeline input: False
Accept wildcard characters: FalseThis cmdlet supports the common parameters: -Debug, -ErrorAction, -ErrorVariable, -InformationAction, -InformationVariable, -OutVariable, -OutBuffer, -PipelineVariable, -Verbose, -WarningAction, and -WarningVariable. For more information, see about_CommonParameters.