New-DbBulkCopy.ps1
|
<#
.SYNOPSIS Efficiently bulk loads data into a SQL Server table. .DESCRIPTION Bulk loads data. Input columns do not need to be in the same order as the destination table as mapping is done. .PARAMETER InputObject A connection string, SqlConnection, SqlCommand, or SqlTransaction. .PARAMETER Data A DataSet or a DataTable (such as from Get-DbData). .PARAMETER Timeout Bulk copy timeout. .PARAMETER PassThru Pass the input on in the pipeline for further operations. .PARAMETER Options A combination of special options from System.Data.SqlClient.SqlBulkCopyOptions. .INPUTS Pipe in the output of Get-DbData or similar. .OUTPUTS (Optionally) Whatever was piped in. .EXAMPLE $serverInstance = ".\SQL2016" New-DbConnection $serverInstance | New-DbCommand "If Object_Id('dbo.Moo', 'U') Is Not Null Drop Table dbo.Moo; Create Table dbo.Moo (A Int Identity (1, 1) Primary Key, B Nvarchar(Max)); Dbcc Checkident('dbo.Moo', Reseed, 100);" | Get-DbData -OutputAs NonQuery | Out-Null $dbData = New-DbConnection $serverInstance | New-DbCommand "Select * From dbo.Moo;" | Get-DbData -OutputAs DataTables -TableMapping @("Moo") $dbData.Alter(@{ B = "A" }) | Out-Null $dbData.Alter(@{ B = "B" }) | Out-Null $dbData.Alter(@{ A = 100; B = "C" }) | Out-Null $dbData.Alter(@{ B = "D" }) | Out-Null New-DbConnection $serverInstance | New-DbCommand "Truncate Table dbo.Moo;" | Get-DbData -OutputAs NonQuery | Out-Null New-DbConnection $serverInstance | New-DbBulkCopy -Data $dbData -Option "KeepIdentity" New-DbConnection $serverInstance | New-DbCommand "Select * From dbo.Moo;" | Get-DbData .NOTES #> function New-DbBulkCopy { [CmdletBinding()] param ( [Parameter(Mandatory = $true, ValueFromPipeline = $true)] [Alias("SqlTransaction")] [Alias("SqlCommand")] [Alias("SqlConnection")] [Alias("ConnectionString")] $InputObject, # Data in either a DataSet or DataTable. [Alias("DataSet")] [Alias("DataTable")] $Data, [System.Data.SqlClient.SqlBulkCopyOptions] $Options = [System.Data.SqlClient.SqlBulkCopyOptions]::Default, $Timeout, [switch] $PassThru ) begin { } process { Use-DbRetry { $closeConnection = $false try { if ($InputObject -is [string]) { $bulkCopy = New-Object System.Data.SqlClient.SqlBulkCopy($InputObject, $Options) } elseif ($InputObject -is [System.Data.SqlClient.SqlConnection]) { if ($InputObject.State -ne "Open") { $InputObject.Open() $closeConnection = $true } $bulkCopy = New-Object System.Data.SqlClient.SqlBulkCopy($InputObject, $Options, $null) } elseif ($InputObject -is [System.Data.SqlClient.SqlCommand]) { if ($InputObject.Connection.State -ne "Open") { $InputObject.Connection.Open() $closeConnection = $true } $bulkCopy = New-Object System.Data.SqlClient.SqlBulkCopy($InputObject.Connection, $Options, $InputObject.Transaction) } else { Write-Error "InputObject was $($InputObject.GetType().FullName) which is an unsupported type" } if ($Timeout) { $bulkCopy.BulkCopyTimeout = $Timeout } if ($Data -is [System.Data.DataSet]) { $tables = $Data.Tables } else { $tables = ,$Data } foreach ($table in $tables) { $bulkCopy.DestinationTableName = $table.TableName # Required in case we've added columns, they will not be in order, and as long As you specify the names here it will all work okay $bulkCopy.ColumnMappings.Clear() $table.Columns | ForEach-Object { [void] $bulkCopy.ColumnMappings.Add((New-Object System.Data.SqlClient.SqlBulkCopyColumnMapping($_.ColumnName, $_.ColumnName))) } $bulkCopy.WriteToServer($table) } $bulkCopy.Close() } finally { if ($closeConnection) { if ($InputObject -is [System.Data.SqlClient.SqlConnection] -and $InputObject.State -eq "Open") { $InputObject.Close() } elseif ($InputObject -is [System.Data.SqlClient.SqlCommand] -and $InputObject.Connection.State -eq "Open") { $InputObject.Connection.Close() } } } } if ($PassThru) { $InputObject } } end { } } |