If your requirements allow for the table to be the same name on your destination as it is on the source,  you can utilize the dbatools module to help make maintaining (and in some cases performing) the process much easier. The command that would allow you to easily perform this task is: Copy-Dbatable.


Import-Module dbatools

$SourceServer = 'svrname1'
$SourceDatabase = 'dbname1'
$Table = 'tblname1'
$DestServer = 'srvname2'
$DestDatabase = 'dbname2'
$SelectQuery = "SELECT * FROM $SourceTable WHERE (<some restriction>)"

Copy-DbaTableData -SqlInstance $SourceServer -Destination $DestServer -DestinationDatabase $DestDatabase -Table $Table -Query $SelectQuery


Now, if it does not and you still need to keep all the same parameters, the module can still help to simply your code:


# Here is the code:
Set-ExecutionPolicy -ExecutionPolicy Unrestricted -Scope Process
Import-Module dbatools

$SourceServer = 'svrname1'
$SourceDatabase = 'dbname1'
$SourceTable = 'tblname1'
$DestServer = 'svrname2'
$DestDatabase = 'dbname2'
$DestTable = 'tblname2'

$SelectQuery = "SELECT * FROM $SourceTable WHERE (some restriction..not important to discussion)"
$SelectData = (Connect-DbaInstance -SqlInstance $SourceServer).Databases[$SourceDatabase].Query($SelectQuery)

<# As long as your data types are supported well in BulkInsert, this should work #>
$SelectData | select-object -ExcludeProperty RowError,RowState,Table,ItemArray,HasErrors |
    Write-DbaDataTable -SqlInstance $DestServer -Table $DestTable