Dynamic SQL Where condition, in Powershell script

Question

I am working on a Powershell script where output of query #1 is where condition feed for query #2 but it is not getting the feed, if someone please see and let me know what could be possible solution s for this.

Also please note, in real environment, both the queries are being run on different instances and no possibilities of linked server

Below example is what I was trying in the AdventureWorks database:

$instance="WIN2016-SQL01\SQLSERVER_01"
$database = "AdventureWorks2014"
$query1 = "SELECT TOP 10 [BusinessEntityID]   FROM [AdventureWorks2014].[Person].[BusinessEntityAddress] where BusinessEntityID < 10 order by 1 "
$Q1 = (invoke-sqlcmd -query $query1 -ServerInstance $instance -Database $database)

$query2 = "SELECT *   FROM [AdventureWorks2014].[Person].[Person] where BusinessEntityID in ($Q1)"
$Q2 = invoke-sqlcmd -query $query2 -ServerInstance $instance -Database $database 

Answer 1

Or you can just build a delimited string to substitute for your IN clause:

$instance="localhost"
$database = "AdventureWorks2017"
$query1 = "SELECT TOP 10 [BusinessEntityID] FROM [Person].[BusinessEntityAddress] where BusinessEntityID < 10 order by 1 "
$Q1 = (invoke-sqlcmd -query $query1 -ServerInstance $instance -Database $database)

$ids = ""
foreach ($r in $Q1)
{
  $ids += ","  + $r.BusinessEntityID
}

$ids = $ids.Substring(1)

$query2 = "SELECT * FROM [Person].[Person] where BusinessEntityID in ($ids)"
$Q2 = invoke-sqlcmd -query $query2 -ServerInstance $instance -Database $database 

$Q2 | format-table