JOIN tables from different SSMS server tables in Powershell

Hello all. Not sure if this can be done (though usually, with computers, all things can be done lol) but I thought I would ask anyways.

I have two servers that cannot communicate with one another. One server has a temp table that I created, the other is a standard table.

I was wondering if PowerShell could select records from the temp table, then the standard table, and join them together using a third SQL statement maybe within PowerShell?

Yes, you use standard TSQL commands under invoke-SQLcmd of the SQLPS module…

Yes, this can be done. Assuming the system you are executing on can communicate with both servers, you could query the server with the temp table and copy the table to the main SQL server. There are some simple commmands you can use if you have the SQL Management Tools installed to leverage the SQLPS module:

https://www.mssqltips.com/sqlservertip/4627/new-powershell-cmdlets-to-read-and-write-sql-server-tables/

You can also get both recordset and join in Powershell, but there isn’t really native functionality. Join-Object function can be use, but if SQL can be used it’s much better to do it there.