Copy-DbaDbTableData, Unable to copy data using a sql query but working when copying data from table to table, attaching debug level logs

[shsharmaivp]

Hi,
I'm using Copy-DbaDbTableData function to move data between tables, there is also a query option that will enable us to move data returned from the query to the destination table. But in my case, it is not working.
When I use the source and destination table options (not query) then it is working fine.
Attaching the debug level logs for both scenarios.

Moving Data from Table to Table

$ copy-DbaDbTableData -SqlInstance "192.168.0.102" -Database "Nikhil" -SqlCredential $Cred -Table "[dbo].[SK_Testing_HPS]"  -DestinationTable "copyDataTable" -AutoCreateTable -Debug                                                                                 
DEBUG: 4735 | [14:42:28][Connect-DbaInstance] Starting process block
DEBUG: 4746 | [14:42:28][Connect-DbaInstance] Immediately checking for Azure
DEBUG: 4634 | [14:42:28][Test-Azure] Test for Azure is negative
DEBUG: 4753 | [14:42:28][Connect-DbaInstance] Starting loop for '192.168.0.102': ComputerName = '192.168.0.102', InstanceName = 'MSSQLSERVER', IsLocalHost = 'False', Type = 'Default'
DEBUG: 4857 | [14:42:28][Connect-DbaInstance] String is passed in, will build server object from instance object and other parameters, do some checks and then return the server object
DEBUG: 4634 | [14:42:28][Test-Azure] Test for Azure is negative
DEBUG: 4965 | [14:42:28][Connect-DbaInstance] authentication method is 'local sql'
DEBUG: 5004 | [14:42:28][Connect-DbaInstance] ApplicationName will be set to 'dbatools PowerShell module - dbatools.io'
DEBUG: 5036 | [14:42:28][Connect-DbaInstance] ConnectionTimeout will be set to '15'
DEBUG: 5042 | [14:42:28][Connect-DbaInstance] Database will be set to 'Nikhil'
DEBUG: 5066 | [14:42:28][Connect-DbaInstance] PacketSize will be set to '4096'
DEBUG: 5085 | [14:42:28][Connect-DbaInstance] Pooled will be set to 'True'
DEBUG: 5094 | [14:42:28][Connect-DbaInstance] SecurePassword will be set
DEBUG: 5120 | [14:42:28][Connect-DbaInstance] UserName will be set to 'sa'
DEBUG: 5154 | [14:42:28][Connect-DbaInstance] Building ServerConnection from SqlConnectionInfo
DEBUG: 5156 | [14:42:28][Connect-DbaInstance] ServerConnection was built
DEBUG: 5174 | [14:42:28][Connect-DbaInstance] Building Server from ServerConnection
DEBUG: 5176 | [14:42:28][Connect-DbaInstance] Server was built
DEBUG: 5195 | [14:42:28][Connect-DbaInstance] Setting ConnectionContext.StatementTimeout to '0'
DEBUG: 5200 | [14:42:28][Connect-DbaInstance] The masked server.ConnectionContext.ConnectionString is Data Source=192.168.0.102;Initial Catalog=Nikhil;User ID=sa;Password=********;Multiple Active Result Sets=False;Encrypt=False;Trust Server Certificate=False;Packet Size=4096;Application Name="dbatools PowerShell module - dbatools.io"
DEBUG: 5210 | [14:42:28][Connect-DbaInstance] We connect to the instance by running SELECT 'dbatools is opening a new connection'
DEBUG: 5219 | [14:42:28][Connect-DbaInstance] We have a connected server object
DEBUG: 5265 | [14:42:28][Connect-DbaInstance] We will set server.ComputerName to server.NetName
DEBUG: 5271 | [14:42:28][Connect-DbaInstance] ComputerName will be set to mycomputer
DEBUG: 4634 | [14:42:28][Test-Azure] Test for Azure is negative
DEBUG: 5281 | [14:42:28][Connect-DbaInstance] We added IsAzure = 'False', DbaInstanceName = instance.InstanceName = 'MSSQLSERVER', SqlInstance = server.DomainInstanceName = '', NetPort = instance.Port = '1433', ConnectedAs = server.ConnectionContext.TrueLogin = 'sa'
DEBUG: 5284 | [14:42:28][Connect-DbaInstance] We return the server object
DEBUG: 5312 | [14:42:28][Connect-DbaInstance] SetDefaultInitFields will be used
DEBUG: 136465 | [14:42:28][Add-ConnectionHashValue] Adding to connection hash
DEBUG: 5341 | [14:42:28][Connect-DbaInstance] We are finished with this instance
DEBUG: 4735 | [14:42:28][Connect-DbaInstance] Starting process block
DEBUG: 4746 | [14:42:28][Connect-DbaInstance] Immediately checking for Azure
DEBUG: 4634 | [14:42:28][Test-Azure] Test for Azure is negative
DEBUG: 4753 | [14:42:28][Connect-DbaInstance] Starting loop for 'mycomputer': ComputerName = 'mycomputer', InstanceName = 'MSSQLSERVER', IsLocalHost = 'False', Type = 'Server'
DEBUG: 4838 | [14:42:28][Connect-DbaInstance] Server object passed in, will do some checks and then return the original object
DEBUG: 5200 | [14:42:28][Connect-DbaInstance] The masked server.ConnectionContext.ConnectionString is Data Source=192.168.0.102;Initial Catalog=Nikhil;User ID=sa;Password=********;Multiple Active Result Sets=False;Encrypt=False;Trust Server Certificate=False;Packet Size=4096;Application Name="dbatools PowerShell module - dbatools.io"
DEBUG: 5210 | [14:42:28][Connect-DbaInstance] We connect to the instance by running SELECT 'dbatools is opening a new connection'
DEBUG: 5219 | [14:42:28][Connect-DbaInstance] We have a connected server object
DEBUG: 4634 | [14:42:28][Test-Azure] Test for Azure is negative
DEBUG: 5281 | [14:42:28][Connect-DbaInstance] We added IsAzure = 'False', DbaInstanceName = instance.InstanceName = 'MSSQLSERVER', SqlInstance = server.DomainInstanceName = '', NetPort = instance.Port = '1433', ConnectedAs = server.ConnectionContext.TrueLogin = 'sa'
DEBUG: 5284 | [14:42:28][Connect-DbaInstance] We return the server object
DEBUG: 5312 | [14:42:28][Connect-DbaInstance] SetDefaultInitFields will be used
DEBUG: 136465 | [14:42:28][Add-ConnectionHashValue] Adding to connection hash
DEBUG: 5341 | [14:42:28][Connect-DbaInstance] We are finished with this instance
DEBUG: 42985 | [14:42:29][Get-DbaDbTable] Processing [Nikhil]
DEBUG: 4735 | [14:42:29][Connect-DbaInstance] Starting process block
DEBUG: 4746 | [14:42:29][Connect-DbaInstance] Immediately checking for Azure
DEBUG: 4634 | [14:42:29][Test-Azure] Test for Azure is negative
DEBUG: 4753 | [14:42:29][Connect-DbaInstance] Starting loop for 'mycomputer': ComputerName = 'mycomputer', InstanceName = 'MSSQLSERVER', IsLocalHost = 'False', Type = 'Server'
DEBUG: 4838 | [14:42:29][Connect-DbaInstance] Server object passed in, will do some checks and then return the original object
DEBUG: 5200 | [14:42:29][Connect-DbaInstance] The masked server.ConnectionContext.ConnectionString is Data Source=192.168.0.102;Initial Catalog=Nikhil;User ID=sa;Password=********;Multiple Active Result Sets=False;Encrypt=False;Trust Server Certificate=False;Packet Size=4096;Application Name="dbatools PowerShell module - dbatools.io"
DEBUG: 5210 | [14:42:29][Connect-DbaInstance] We connect to the instance by running SELECT 'dbatools is opening a new connection'
DEBUG: 5219 | [14:42:29][Connect-DbaInstance] We have a connected server object
DEBUG: 4634 | [14:42:29][Test-Azure] Test for Azure is negative
DEBUG: 5281 | [14:42:29][Connect-DbaInstance] We added IsAzure = 'False', DbaInstanceName = instance.InstanceName = 'MSSQLSERVER', SqlInstance = server.DomainInstanceName = '', NetPort = instance.Port = '1433', ConnectedAs = server.ConnectionContext.TrueLogin = 'sa'
DEBUG: 5284 | [14:42:29][Connect-DbaInstance] We return the server object
DEBUG: 5312 | [14:42:29][Connect-DbaInstance] SetDefaultInitFields will be used
DEBUG: 136465 | [14:42:29][Add-ConnectionHashValue] Adding to connection hash
DEBUG: 5341 | [14:42:29][Connect-DbaInstance] We are finished with this instance
DEBUG: 4735 | [14:42:29][Connect-DbaInstance] Starting process block
DEBUG: 4746 | [14:42:29][Connect-DbaInstance] Immediately checking for Azure
DEBUG: 4634 | [14:42:29][Test-Azure] Test for Azure is negative
DEBUG: 4753 | [14:42:29][Connect-DbaInstance] Starting loop for 'mycomputer': ComputerName = 'mycomputer', InstanceName = 'MSSQLSERVER', IsLocalHost = 'False', Type = 'Server'
DEBUG: 4838 | [14:42:29][Connect-DbaInstance] Server object passed in, will do some checks and then return the original object
DEBUG: 5200 | [14:42:29][Connect-DbaInstance] The masked server.ConnectionContext.ConnectionString is Data Source=192.168.0.102;Initial Catalog=Nikhil;User ID=sa;Password=********;Multiple Active Result Sets=False;Encrypt=False;Trust Server Certificate=False;Packet Size=4096;Application Name="dbatools PowerShell module - dbatools.io"
DEBUG: 5210 | [14:42:29][Connect-DbaInstance] We connect to the instance by running SELECT 'dbatools is opening a new connection'
DEBUG: 5219 | [14:42:29][Connect-DbaInstance] We have a connected server object
DEBUG: 4634 | [14:42:29][Test-Azure] Test for Azure is negative
DEBUG: 5281 | [14:42:29][Connect-DbaInstance] We added IsAzure = 'False', DbaInstanceName = instance.InstanceName = 'MSSQLSERVER', SqlInstance = server.DomainInstanceName = '', NetPort = instance.Port = '1433', ConnectedAs = server.ConnectionContext.TrueLogin = 'sa'
DEBUG: 5284 | [14:42:29][Connect-DbaInstance] We return the server object
DEBUG: 5312 | [14:42:29][Connect-DbaInstance] SetDefaultInitFields will be used
DEBUG: 136465 | [14:42:29][Add-ConnectionHashValue] Adding to connection hash
DEBUG: 5341 | [14:42:29][Connect-DbaInstance] We are finished with this instance
DEBUG: 42985 | [14:42:29][Get-DbaDbTable] Processing [Nikhil]

Confirm
Are you sure you want to perform this action?
Performing the operation "Copy data from [Nikhil].[dbo].[SK_Testing_HPS]" on target "[192.168.0.102]".
[Y] Yes  [A] Yes to All  [N] No  [L] No to All  [S] Suspend  [?] Help (default is "Y"): y

Confirm
Are you sure you want to perform this action?
Performing the operation "Writing rows to [Nikhil].[dbo].[copyDataTable]" on target "[192.168.0.102]".
[Y] Yes  [A] Yes to All  [N] No  [L] No to All  [S] Suspend  [?] Help (default is "Y"): y
DEBUG: 12024 | [14:42:31][Copy-DbaDbTableData] 112 rows inserted in 0.4 sec

SourceInstance        : 192.168.0.102
SourceDatabase        : Nikhil
SourceDatabaseID      : 29
SourceSchema          : dbo
SourceTable           : SK_Testing_HPS
DestinationInstance   : 192.168.0.102
DestinationDatabase   : Nikhil
DestinationDatabaseID : 29
DestinationSchema     : dbo
DestinationTable      : copyDataTable
RowsCopied            : 112
Elapsed               : 418.63 ms

Moving data using SQL Query

$ copy-DbaDbTableData -SqlInstance "192.168.0.102" -Database "Nikhil" -SqlCredential $Cred -DestinationTable "[dbo].[copytable2]" -Query "select * from [dbo].[SK_Testing_HPS];" -AutoCreateTable -Truncate -Debug

DEBUG: 4735 | [14:37:03][Connect-DbaInstance] Starting process block
DEBUG: 4746 | [14:37:03][Connect-DbaInstance] Immediately checking for Azure
DEBUG: 4634 | [14:37:03][Test-Azure] Test for Azure is negative
DEBUG: 4753 | [14:37:03][Connect-DbaInstance] Starting loop for '192.168.0.102': ComputerName = '192.168.0.102', InstanceName = 'MSSQLSERVER', IsLocalHost = 'False', Type = 'Default'
DEBUG: 4857 | [14:37:03][Connect-DbaInstance] String is passed in, will build server object from instance object and other parameters, do some checks and then return the server object
DEBUG: 4634 | [14:37:03][Test-Azure] Test for Azure is negative
DEBUG: 4965 | [14:37:03][Connect-DbaInstance] authentication method is 'local sql'
DEBUG: 5004 | [14:37:03][Connect-DbaInstance] ApplicationName will be set to 'dbatools PowerShell module - dbatools.io'
DEBUG: 5036 | [14:37:03][Connect-DbaInstance] ConnectionTimeout will be set to '15'
DEBUG: 5042 | [14:37:03][Connect-DbaInstance] Database will be set to 'Nikhil'
DEBUG: 5066 | [14:37:03][Connect-DbaInstance] PacketSize will be set to '4096'
DEBUG: 5085 | [14:37:03][Connect-DbaInstance] Pooled will be set to 'True'
DEBUG: 5094 | [14:37:03][Connect-DbaInstance] SecurePassword will be set
DEBUG: 5120 | [14:37:03][Connect-DbaInstance] UserName will be set to 'sa'
DEBUG: 5154 | [14:37:03][Connect-DbaInstance] Building ServerConnection from SqlConnectionInfo
DEBUG: 5156 | [14:37:03][Connect-DbaInstance] ServerConnection was built
DEBUG: 5174 | [14:37:03][Connect-DbaInstance] Building Server from ServerConnection
DEBUG: 5176 | [14:37:03][Connect-DbaInstance] Server was built
DEBUG: 5195 | [14:37:03][Connect-DbaInstance] Setting ConnectionContext.StatementTimeout to '0'
DEBUG: 5200 | [14:37:03][Connect-DbaInstance] The masked server.ConnectionContext.ConnectionString is Data Source=192.168.0.102;Initial Catalog=Nikhil;User ID=sa;Password=********;Multiple Active Result Sets=False;Encrypt=False;Trust Server Certificate=False;Packet Size=4096;Application Name="dbatools PowerShell module - dbatools.io"
DEBUG: 5210 | [14:37:03][Connect-DbaInstance] We connect to the instance by running SELECT 'dbatools is opening a new connection'
DEBUG: 5219 | [14:37:03][Connect-DbaInstance] We have a connected server object
DEBUG: 5265 | [14:37:03][Connect-DbaInstance] We will set server.ComputerName to server.NetName
DEBUG: 5271 | [14:37:03][Connect-DbaInstance] ComputerName will be set to mycomputer
DEBUG: 4634 | [14:37:03][Test-Azure] Test for Azure is negative
DEBUG: 5281 | [14:37:03][Connect-DbaInstance] We added IsAzure = 'False', DbaInstanceName = instance.InstanceName = 'MSSQLSERVER', SqlInstance = server.DomainInstanceName = '', NetPort = instance.Port = '1433', ConnectedAs = server.ConnectionContext.TrueLogin = 'sa'
DEBUG: 5284 | [14:37:03][Connect-DbaInstance] We return the server object
DEBUG: 5312 | [14:37:03][Connect-DbaInstance] SetDefaultInitFields will be used
DEBUG: 136465 | [14:37:03][Add-ConnectionHashValue] Adding to connection hash
DEBUG: 5341 | [14:37:03][Connect-DbaInstance] We are finished with this instance

This it, logs stoped here for query option.

[shsharmaivp]

copy-DbaDbTableData -SqlInstance "192.168.0.102" -Database "testdb" -SqlCredential $Cred -DestinationTable "[dbo].[copytable3]" -View "[dbo].[customViewCheck]" -Query "select * from dbo.customViewCheck where average_cost_base_eod < 3" -AutoCreateTable

copy-DbaDbTableData -SqlInstance "192.168.0.102" -Database "testdb" -SqlCredential $Cred -DestinationTable "[dbo].[copytable3]" -View "[dbo].[customViewCheck]" -Query "select * from dbo.customViewCheck where average_cost_base_eod < 3" -AutoCreateTable

SourceInstance        : 192.168.0.102
SourceDatabase        : testdb
SourceDatabaseID      : 9
SourceSchema          : dbo
SourceTable           : customViewCheck
DestinationInstance   : 192.168.0.102
DestinationDatabase   : testdb
DestinationDatabaseID : 9
DestinationSchema     : dbo
DestinationTable      : copytable3
RowsCopied            : 7512
Elapsed               : 138.85 ms

When the Source Table or View parameter is provided, it is working correctly (copying data from the query). However, I would like to omit the Table or View parameter and want to pass only the query parameter to copy data to the destination table. how can i achieve this.