function Copy-DbaDbTableData {

<#

[CmdletBinding(DefaultParameterSetName = "Default", SupportsShouldProcess)]

param (

[DbaInstanceParameter]$SqlInstance,

[PSCredential]$SqlCredential,

[DbaInstanceParameter[]]$Destination,

[PSCredential]$DestinationSqlCredential,

[string]$Database,

[string]$DestinationDatabase,

[string[]]$Table,

[string[]]$View, # Copy-DbaDbTableData and Copy-DbaDbViewData are consolidated to reduce maintenance cost, so this param is specific to calls of Copy-DbaDbViewData

[string]$Query,

[switch]$ForceExplicitMapping,

[switch]$AutoCreateTable,

[int]$BatchSize = 50000,

[int]$NotifyAfter = 5000,

[string]$DestinationTable,

[switch]$NoTableLock,

[switch]$CheckConstraints,

[switch]$FireTriggers,

[switch]$KeepIdentity,

[switch]$KeepNulls,

[switch]$Truncate,

[int]$BulkCopyTimeout = 5000,

[int]$CommandTimeout = 0,

[switch]$UseDefaultFileGroup,

[Microsoft.SqlServer.Management.Smo.ScriptingOptions]$ScriptingOptionsObject,

[Parameter(ValueFromPipeline)]

[Microsoft.SqlServer.Management.Smo.TableViewBase[]]$InputObject,

[switch]$EnableException

)

begin {

$bulkCopyOptions = 0

$options = "TableLock", "CheckConstraints", "FireTriggers", "KeepIdentity", "KeepNulls", "Default"

foreach ($option in $options) {

$optionValue = Get-Variable $option -ValueOnly -ErrorAction SilentlyContinue

if ($option -eq "TableLock" -and (!$NoTableLock)) {

$optionValue = $true

}

if ($optionValue -eq $true) {

$bulkCopyOptions += $([Microsoft.Data.SqlClient.SqlBulkCopyOptions]::$option).value__

}

}

if (Test-Bound -ParameterName ScriptingOptionsObject) {

$defaultFGScriptingOption = @{

ScriptingOptionsObject = $ScriptingOptionsObject

}

} else {

$defaultFGScriptingOption = @{

ScriptingOptionsObject = $(

$so = New-DbaScriptingOption

$so.NoFileGroup = $UseDefaultFileGroup

$so

)

}

}

}

process {

if ((Test-Bound -Not -ParameterName InputObject) -and ((Test-Bound -Not -ParameterName SqlInstance, Database -And) -or (Test-Bound -Not -ParameterName Table, View))) {

if (Test-Bound -ParameterName Query) {

# Without the hint at Query the message reads as if SqlInstance or Database were missing (see #10676).

Stop-Function -Message "When using Query, you still have to specify SqlInstance, Database and [View|Table]. The query determines the data that is copied, but the command needs the table or view as the source object for its metadata."

} else {

Stop-Function -Message "You must pipe in a table or specify SqlInstance, Database and [View|Table]."

}

return

}

# determine if -Table or -View was used

$SourceObject = $Table

if ((Test-Bound -ParameterName View) -and (Test-Bound -ParameterName Table)) {

Stop-Function -Message "Only one of [View|Table] may be specified."

return

} elseif ( Test-Bound -ParameterName View ) {

$SourceObject = $View

}

if ($SqlInstance) {

if ((Test-Bound -Not -ParameterName Destination, DestinationDatabase, DestinationTable)) {

Stop-Function -Message "Cannot copy $SourceObject into itself. One of the parameters Destination (Server), DestinationDatabase, or DestinationTable must be specified " -Target $SourceObject

return

}

try {

# Ensuring that the default db connection is to the passed in $Database instead of the master db. This way callers don't have to remember to do 3 part queries.

$server = Connect-DbaInstance -SqlInstance $SqlInstance -SqlCredential $SqlCredential -Database $Database

} catch {

Stop-Function -Message "Failure" -Category ConnectionError -ErrorRecord $_ -Target $SqlInstance

return

}

try {

foreach ($sourceDataObject in $SourceObject) {

$dbObject = $null

if ( Test-Bound -ParameterName View ) {

$dbObject = Get-DbaDbView -SqlInstance $server -View $sourceDataObject -Database $Database -EnableException -Verbose:$false

} else {

$dbObject = Get-DbaDbTable -SqlInstance $server -Table $sourceDataObject -Database $Database -EnableException -Verbose:$false

}

if ($dbObject.Count -eq 1) {

$InputObject += $dbObject

} else {

Stop-Function -Message "The object $sourceDataObject matches $($dbObject.Count) objects. Unable to determine which object to copy" -Continue

}

}

} catch {

Stop-Function -Message "Unable to determine source : $SourceObject"

return

}

}

foreach ($sqlObject in $InputObject) {

$Database = $sqlObject.Parent.Name

$server = $sqlObject.Parent.Parent

if ((Test-Bound -Not -ParameterName DestinationTable)) {

$DestinationTable = '[' + $sqlObject.Schema + '].[' + $sqlObject.Name + ']'

}

$newTableParts = Get-ObjectNameParts -ObjectName $DestinationTable

#using FQTN to determine database name

if ($newTableParts.Database) {

$DestinationDatabase = $newTableParts.Database

} elseif ((Test-Bound -Not -ParameterName DestinationDatabase)) {

$DestinationDatabase = $Database

}

if (-not $Destination) {

$Destination = $server

}

foreach ($destinstance in $Destination) {

try {

$destServer = Connect-DbaInstance -SqlInstance $destinstance -SqlCredential $DestinationSqlCredential -Database $DestinationDatabase

} catch {

Stop-Function -Message "Failure" -Category ConnectionError -ErrorRecord $_ -Target $destinstance -Continue

}

if ($DestinationDatabase -notin $destServer.Databases.Name) {

Stop-Function -Message "Database $DestinationDatabase doesn't exist on $destServer"

return

}

$desttable = Get-DbaDbTable -SqlInstance $destServer -Table $DestinationTable -Database $DestinationDatabase -Verbose:$false | Select-Object -First 1

if (-not $desttable -and $AutoCreateTable) {

try {

$tablescript = $null

$schemaNameToReplace = $null

$tableNameToReplace = $null

$isExternalTableScript = $false

$isExternalTable = $false

if (Test-Bound -Not -ParameterName View) {

try {

$isExternalTable = $sqlObject.IsExternal

} catch {

# Older SMO builds can throw while retrieving IsExternal, so preserve the standard scripting path.

Write-Message -Level Debug -Message "Unable to retrieve IsExternal for $sqlObject; using standard table scripting. Error: $_"

}

}

if ( Test-Bound -ParameterName View ) {

#select view into tempdb to generate script

$tempTableName = "$($sqlObject.Name)_table"

$createquery = "SELECT * INTO tempdb..$tempTableName FROM [$($sqlObject.Schema)].[$($sqlObject.Name)] WHERE 1=2"

Invoke-DbaQuery -SqlInstance $server -Database $Database -Query $createquery -EnableException

#refreshing table list to make sure get-dbadbtable will find the new table

$server.Databases['tempdb'].Tables.Refresh($true)

$tempTable = Get-DbaDbTable -SqlInstance $server -Database tempdb -Table $tempTableName

# need these for generating the script of the table and then replacing the schema and name

$schemaNameToReplace = $tempTable.Schema

$tableNameToReplace = $tempTable.Name

$tablescript = $tempTable |

Export-DbaScript @defaultFGScriptingOption -Passthru |

Out-String

# cleanup

Invoke-DbaQuery -SqlInstance $server -Database $Database -Query "DROP TABLE tempdb..$tempTableName" -EnableException

} elseif ($isExternalTable) {

$columnDefinitions = foreach ($column in $sqlObject.Columns) {

$columnName = $column.Name.Replace("]", "]]")

$dataType = $column.DataType

$dataTypeName = $dataType.Name

$sqlDataType = $dataType.SqlDataType.ToString()

if ([string]::IsNullOrEmpty($dataTypeName) -or [string]::IsNullOrEmpty($sqlDataType)) {

throw "Unable to render data type for external table column $($column.Name)."

}

switch -Regex ($sqlDataType) {

"^(Binary|Char|NChar|NVarChar|VarBinary|VarChar)$" {

if ($dataType.MaximumLength -lt 0) {

$dataTypeDeclaration = "$dataTypeName(max)"

} else {

$dataTypeDeclaration = "$dataTypeName($($dataType.MaximumLength))"

}

break

}

"^(NVarCharMax|VarBinaryMax|VarCharMax)$" {

$dataTypeDeclaration = "$dataTypeName(max)"

break

}

"^(Decimal|Numeric)$" {

$dataTypeDeclaration = "$dataTypeName($($dataType.NumericPrecision),$($dataType.NumericScale))"

break

}

"^(DateTime2|DateTimeOffset|Time)$" {

$dataTypeDeclaration = "$dataTypeName($($dataType.NumericScale))"

break

}

"Float" {

if ($dataType.NumericPrecision -gt 0) {

$dataTypeDeclaration = "$dataTypeName($($dataType.NumericPrecision))"

} else {

$dataTypeDeclaration = $dataTypeName

}

break

}

"^(UserDefinedDataType|UserDefinedType)$" {

$escapedTypeName = $dataTypeName.Replace("]", "]]")

if ($dataType.Schema) {

$escapedTypeSchema = $dataType.Schema.Replace("]", "]]")

$dataTypeDeclaration = "[$escapedTypeSchema].[$escapedTypeName]"

} else {

$dataTypeDeclaration = "[$escapedTypeName]"

}

break

}

"Vector" {

if ($dataType.VectorDimensions -le 0) {

throw "Unable to render vector dimensions for external table column $($column.Name)."

}

if ($dataType.VectorBaseType) {

$dataTypeDeclaration = "$dataTypeName($($dataType.VectorDimensions), $($dataType.VectorBaseType))"

} else {

$dataTypeDeclaration = "$dataTypeName($($dataType.VectorDimensions))"

}

break

}

default {

$dataTypeDeclaration = $dataTypeName

}

}

if ($column.Collation) {

$collationClause = " COLLATE $($column.Collation)"

} else {

$collationClause = ""

}

if ($column.Nullable) {

$nullability = "NULL"

} else {

$nullability = "NOT NULL"

}

" [$columnName] $dataTypeDeclaration$collationClause $nullability"

}

if (-not $columnDefinitions) {

throw "Unable to render external table $sqlObject because it has no columns."

}

if ($newTableParts.Schema) {

$targetSchema = $newTableParts.Schema

} else {

$targetSchema = $sqlObject.Schema

}

if ($newTableParts.Name) {

$targetName = $newTableParts.Name

} else {

$targetName = $sqlObject.Name

}

$escapedSchema = $targetSchema.Replace("]", "]]")

$escapedName = $targetName.Replace("]", "]]")

$tablescript = "CREATE TABLE [$escapedSchema].[$escapedName] (`n$($columnDefinitions -join ",`n")`n);"

$isExternalTableScript = $true

} else {

$tablescript = $sqlObject |

Export-DbaScript @defaultFGScriptingOption -Passthru |

Out-String

$schemaNameToReplace = $sqlObject.Schema

$tableNameToReplace = $sqlObject.Name

}

if (-not $isExternalTableScript) {

#replacing table name

if ($newTableParts.Name) {

$rX = "(CREATE|ALTER)( TABLE \[$([regex]::Escape($schemaNameToReplace))\]\.\[)$([regex]::Escape($tableNameToReplace))(\])"

$tablescript = $tablescript -replace $rX, "`${1}`${2}$($newTableParts.Name)`${3}"

}

#replacing table schema

if ($newTableParts.Schema) {

$rX = "(CREATE|ALTER)( TABLE \[)$([regex]::Escape($schemaNameToReplace))(\]\.\[$([regex]::Escape($newTableParts.Name))\])"

$tablescript = $tablescript -replace $rX, "`${1}`${2}$($newTableParts.Schema)`${3}"

}

}

if ($PSCmdlet.ShouldProcess($destServer, "Creating new table: $DestinationTable")) {

Write-Message -Message "New table script: $tablescript" -Level VeryVerbose

Invoke-DbaQuery -SqlInstance $destServer -Database $DestinationDatabase -Query "$tablescript" -EnableException # add some string assurance there

#table list was updated, let's grab a fresh one

$destServer.Databases[$DestinationDatabase].Tables.Refresh()

$desttable = Get-DbaDbTable -SqlInstance $destServer -Table $DestinationTable -Database $DestinationDatabase -Verbose:$false

Write-Message -Message "New table created: $desttable" -Level Verbose

}

} catch {

Stop-Function -Message "Unable to determine destination table: $DestinationTable" -ErrorRecord $_

return

}

}

if (-not $desttable) {

Stop-Function -Message "Table $DestinationTable cannot be found in $DestinationDatabase. Use -AutoCreateTable to automatically create the table on the destination." -Continue

}

if ($server.DatabaseEngineType -eq "SqlAzureDatabase") {

$fqtnfrom = "$sqlObject"

} else {

$fqtnfrom = "$($server.Databases[$Database]).$sqlObject"

}

if ($destServer.DatabaseEngineType -eq "SqlAzureDatabase") {

$fqtndest = "$desttable"

} else {

$fqtndest = "$($destServer.Databases[$DestinationDatabase]).$desttable"

}

if ($fqtndest -eq $fqtnfrom -and $server.Name -eq $destServer.Name -and (Test-Bound -ParameterName Query -Not)) {

Stop-Function -Message "Cannot copy $fqtnfrom on $($server.Name) into $fqtndest on ($destServer.Name). Source and Destination must be different " -Target $Table

return

}

if (Test-Bound -ParameterName Query -Not) {

# Build ORDER BY clause to ensure consistent row order

# This prevents data misalignment when copying tables without explicit ordering

$orderByClause = ""

# Refresh indexes to ensure we have current metadata

$sqlObject.Indexes.Refresh()

# Option 1: Use clustered index columns for ordering (most common and performant)

$clusteredIndex = $sqlObject.Indexes | Where-Object IsClustered -eq $true | Select-Object -First 1

if ($clusteredIndex) {

$orderColumns = $clusteredIndex.IndexedColumns | Sort-Object IndexKeyPosition | ForEach-Object {

$colName = $_.Name

$descending = if ($_.Descending) { " DESC" } else { "" }

"[$colName]$descending"

}

if ($orderColumns) {

$orderByClause = " ORDER BY " + ($orderColumns -join ", ")

Write-Message -Level Verbose -Message "Using clustered index for ordering: $orderByClause"

}

}

# Option 2: If no clustered index, try primary key

if (-not $orderByClause) {

$primaryKey = $sqlObject.Indexes | Where-Object IndexKeyType -eq "DriPrimaryKey" | Select-Object -First 1

if ($primaryKey) {

$orderColumns = $primaryKey.IndexedColumns | Sort-Object IndexKeyPosition | ForEach-Object {

$colName = $_.Name

$descending = if ($_.Descending) { " DESC" } else { "" }

"[$colName]$descending"

}

if ($orderColumns) {

$orderByClause = " ORDER BY " + ($orderColumns -join ", ")

Write-Message -Level Verbose -Message "Using primary key for ordering: $orderByClause"

}

}

}

# Option 3: If using KeepIdentity and an identity column exists, order by it

if (-not $orderByClause -and $KeepIdentity) {

$identityColumn = $sqlObject.Columns | Where-Object Identity -eq $true | Select-Object -First 1

if ($identityColumn) {

$orderByClause = " ORDER BY [$($identityColumn.Name)]"

Write-Message -Level Verbose -Message "Using identity column for ordering: $orderByClause"

}

}

# If no ordering found, log a warning for tables without proper keys

if (-not $orderByClause) {

Write-Message -Level Verbose -Message "No clustered index, primary key, or identity column found for ordering. Row order is not guaranteed."

}

$Query = "SELECT * FROM $fqtnfrom$orderByClause"

$sourceLabel = $fqtnfrom

} else {

$sourceLabel = "Query"

}

$bulkCopyConnection = $null

$cmd = $null

$reader = $null

$bulkCopy = $null

try {

if ($Truncate -eq $true) {

if ($Pscmdlet.ShouldProcess($destServer, "Truncating table $fqtndest")) {

Invoke-DbaQuery -SqlInstance $destServer -Database $DestinationDatabase -Query "TRUNCATE TABLE $fqtndest" -EnableException

}

}

if ($Pscmdlet.ShouldProcess($server, "Copy data from $sourceLabel")) {

$cmd = $server.ConnectionContext.SqlConnectionObject.CreateCommand()

$cmd.CommandTimeout = $CommandTimeout

$cmd.CommandText = $Query

if ($server.ConnectionContext.IsOpen -eq $false) {

$server.ConnectionContext.SqlConnectionObject.Open()

}

$bulkCopyConnection = $destServer.ConnectionContext.SqlConnectionObject.Clone()

$bulkCopyConnection.Open()

$bulkCopy = New-Object Microsoft.Data.SqlClient.SqlBulkCopy($bulkCopyConnection, $bulkCopyOptions, $null)

$bulkCopy.DestinationTableName = $fqtndest

$bulkCopy.EnableStreaming = $true

$bulkCopy.BatchSize = $BatchSize

$bulkCopy.NotifyAfter = $NotifyAfter

$bulkCopy.BulkCopyTimeout = $BulkCopyTimeout

# Get list of writable columns from destination table to avoid insert failures. Computed, rowversion

# and generated always columns (temporal periods, ledger metadata) cannot be written, and they are also

# what breaks the implicit positional mapping of SqlBulkCopy: it counts a computed column (the server

# then rejects the insert) and silently drops the source column that lands on a rowversion column,

# shifting every column behind it by one (see #10661). GeneratedAlwaysType is only supported by SMO on

# SQL Server 2016 and later and on Azure SQL Database, so it has to be guarded the same way. Azure SQL

# Database is named on its own because its product version says 12 no matter what it supports.

$destSupportsGeneratedAlways = $destServer.VersionMajor -ge 13 -or $destServer.DatabaseEngineType -eq "SqlAzureDatabase"

# Refresh the columns collection to ensure it's populated

$desttable.Columns.Refresh()

$destColumns = @($desttable.Columns | Where-Object { -not $PSItem.Computed -and $PSItem.DataType.SqlDataType -ne "Timestamp" -and -not ($destSupportsGeneratedAlways -and $PSItem.GeneratedAlwaysType -ne "None") } | Select-Object -ExpandProperty Name)

Write-Message -Level Verbose -Message "Destination table has $($destColumns.Count) writable columns"

# The legacy bulk copy library uses a 4 byte integer to track the RowsCopied, so the only option is to use

# integer wrap so that copy operations of row counts greater than [int32]::MaxValue will report accurate numbers.

# See https://github.com/dataplat/dbatools/issues/6927 for more details

$script:prevRowsCopied = [int64]0

$script:totalRowsCopied = [int64]0

$elapsed = [System.Diagnostics.Stopwatch]::StartNew()

# Add RowCount output

$bulkCopy.Add_SqlRowsCopied( {

$script:totalRowsCopied += (Get-AdjustedTotalRowsCopied -ReportedRowsCopied $args[1].RowsCopied -PreviousRowsCopied $script:prevRowsCopied).NewRowCountAdded

$tstamp = $(Get-Date -format 'yyyyMMddHHmmss')

Write-Message -Level Verbose -Message "[$tstamp] The bulk copy library reported RowsCopied = $($args[1].RowsCopied). The previous RowsCopied = $($script:prevRowsCopied). The adjusted total rows copied = $($script:totalRowsCopied)"

$RowsPerSec = [math]::Round($script:totalRowsCopied / $elapsed.ElapsedMilliseconds * 1000.0, 1)

Write-Progress -Id 1 -Activity "Inserting rows" -Status ([System.String]::Format("{0} rows ({1} rows/sec)", $script:totalRowsCopied, $RowsPerSec))

# save the previous count of rows copied to be used on the next event notification

$script:prevRowsCopied = $args[1].RowsCopied

})

}

if ($Pscmdlet.ShouldProcess($destServer, "Writing rows to $fqtndest")) {

$reader = $cmd.ExecuteReader()

# Only apply explicit column mapping for straight table copies (not custom queries)

# Custom queries may have different column names/aliases, so they are mapped by position

# Appending -ForceExplicitMapping will override this behaviour and keep explicit column mapping

if (-not (Test-Bound -ParameterName Query) -or $ForceExplicitMapping) {

# Map only columns that exist in both source and destination (excluding computed, rowversion and generated always columns)

for ($i = 0; $i -lt $reader.FieldCount; $i++) {

$sourceColumn = $reader.GetName($i)

if ($destColumns -contains $sourceColumn) {

$null = $bulkCopy.ColumnMappings.Add($sourceColumn, $sourceColumn)

} else {

Write-Message -Level Verbose -Message "Skipping column $sourceColumn (not in destination or not writable)"

}

}

} else {

# Map the query columns by position onto the writable destination columns. This is what SqlBulkCopy does

# on its own, except that its list also contains the computed, rowversion and generated always columns (see above).

if ($reader.FieldCount -gt $destColumns.Count) {

$columnCountMessage = "The query returns $($reader.FieldCount) columns but $fqtndest has only $($destColumns.Count) writable columns. Computed, rowversion and generated always columns cannot be written and do not count."

$reader.Close()

throw $columnCountMessage

}

for ($i = 0; $i -lt $reader.FieldCount; $i++) {

Write-Message -Level Verbose -Message "Mapping query column $i ($($reader.GetName($i))) to destination column $($destColumns[$i])"

$null = $bulkCopy.ColumnMappings.Add($i, $destColumns[$i])

}

}

$bulkCopy.WriteToServer($reader)

$finalRowCountReported = Get-BulkRowsCopiedCount $bulkCopy

# -1 signals that the reflection lookup failed, not a wrapped counter, so it must not

# reach Get-AdjustedTotalRowsCopied: fed in as a row count it inflates the total by

# billions of rows (see #10675). The running total from the notifications is then the

# best number available.

if ($finalRowCountReported -ge 0) {

$script:totalRowsCopied += (Get-AdjustedTotalRowsCopied -ReportedRowsCopied $finalRowCountReported -PreviousRowsCopied $script:prevRowsCopied).NewRowCountAdded

}

$RowsTotal = $script:totalRowsCopied

$TotalTime = [math]::Round($elapsed.Elapsed.TotalSeconds, 1)

Write-Message -Level Verbose -Message "$RowsTotal rows inserted in $TotalTime sec"

if ($RowsTotal -gt 0) {

Write-Progress -Id 1 -Activity "Inserting rows" -Status "Complete" -Completed

}

$server.ConnectionContext.SqlConnectionObject.Close()

$bulkCopy.Close()

$bulkCopy.Dispose()

$reader.Close()

[PSCustomObject]@{

SourceInstance = $server.Name

SourceDatabase = $Database

SourceDatabaseID = $sqlObject.Parent.ID

SourceSchema = $sqlObject.Schema

SourceTable = $sqlObject.Name

DestinationInstance = $destServer.Name

DestinationDatabase = $DestinationDatabase

DestinationDatabaseID = $desttable.Parent.ID

DestinationSchema = $desttable.Schema

DestinationTable = $desttable.Name

RowsCopied = $RowsTotal

Elapsed = [prettytimespan]$elapsed.Elapsed

}

}

} catch {

Stop-Function -Message "Something went wrong" -ErrorRecord $_ -Target $server -continue

} finally {

# The source side is only cleaned up inside the try after a successful WriteToServer. When the bulk

# copy fails, the reader stays open, the SELECT keeps running on the source (waiting on ASYNC_NETWORK_IO

# once the network buffers are full) and holds its schema stability lock on the source table until

# someone kills the session (see #10685). So the leftovers are cleaned up here.

if ($reader -and -not $reader.IsClosed) {

try {

# Cancel the command first: closing a reader with unread rows would otherwise drain the whole

# remaining result set from the source before it returns.

$cmd.Cancel()

$reader.Close()

} catch {

Write-Message -Level Debug -Message "Failed to close the reader on the source: $PSItem"

}

}

if ($bulkCopy) {

$bulkCopy.Close()

$bulkCopy.Dispose()

}

if ($reader -and $server.ConnectionContext.SqlConnectionObject.State -eq "Open") {

$server.ConnectionContext.SqlConnectionObject.Close()

}

if ($bulkCopyConnection) {

$bulkCopyConnection.Close()

$bulkCopyConnection.Dispose()

}

}

}

}

}

}