Copy-DbaDbTableData doesn't copy to correct table if >1 table with same name in different schemas

[Adam-Wenger]

Before submitting a bug report:

• Ensure you are able to reproduce it on the latest released version (0.9.450)
• Verified this bug is not already reported in an issue
• Verified errors are not related to permissions
• Can reproduce in a clean PowerShell session (clean = powershell -NoProfile)

Steps to Reproduce

Copy-DbaDbTableData doesn't copy to correct table if there are >1 schema with the same table name, and you are trying to copy a table that is not the first schema alphabetically.

Expected Behavior

Both the top and bottom call would write to ww schema version of table.

Actual Behavior

If you do not specify a destinationTable explicitly, the code sets (246-248)

if ((Test-Bound -Not -ParameterName DestinationTable)) {
   $DestinationTable = $sqltable.Name
}

Then it calls this: (230)

$desttable = Get-DbaDbTable -SqlInstance 'useomdbn12048,1113' -Table $DestinationTable -Database $Database -EnableException -Verbose:$false | Select-Object -First 1

What happens is that since $sqltable.Name is just the table name, and does not include the schema, the | Select-Object -First 1 ends up pulling the first schema alphabetically with this table name.

I believe the fix would be to change the $DestinationTable = $sqltable.Name line to append the schem name from the $sqltable object as well:

so line 247 would become this:

$DestinationTable = $sqltable.Schema + '.' + $sqltable.Name;

At this point you can likely remove the | Select-Object -First 1 also

Environmental data

PowerShell:
Name Value
PSVersion 5.1.14393.2430
PSEdition Desktop
PSCompatibleVersions {1.0, 2.0, 3.0, 4.0...}
BuildVersion 10.0.14393.2430
CLRVersion 4.0.30319.42000
WSManStackVersion 3.0
PSRemotingProtocolVersion 2.3
SerializationVersion 1.1.0.1

SQL Server:
Microsoft SQL Server 2012 (SP1) - 11.0.3156.0 (X64) May 4 2015 18:48:09 Copyright (c) Microsoft Corporation Developer Edition (64-bit) on Windows NT 6.2 (Build 9200: )

[Adam-Wenger]

Still exists in 0.9.455

[sqllensman]

The $DestinationTable would also need to be wrapped in square brackets [ ]
eg $DestinationTable = '[' + $sqltable.Schema + '].[' + $sqltable.Name + ']'

Also looking at the code the Table parameter is described as accepting a three part name. However as the code also enforces that a database be passed then this does not seem to add any value. If the Table is passed with a database name that differs from the Database parameter then the call to

Get-DbaDbTable -SqlInstance $server -Table $Table -Database $Database

will not return any result.

Another issue that results from determining destination from Source is that no Destination parameters are passed then a call like

Copy-DbaTableData -SqlInstance $sourceServer -Database 'AdventureWorks2014' -Table '[HumanResources].[Department.Copy]' -Truncate

will truncate the Source table

while

Copy-DbaTableData -SqlInstance $sourceServer -Database 'AdventureWorks2014' -Table '[HumanResources].[Department.Copy]'

will attempt to copy the table into itself.

Are either of these two actions intended?