Extracting results from a database and using the results in a query

Question

I am looking to run a query on a SQL server database and then use the results against a set of returned values from another database. query

Let me explain further

I have a list of exclusion names in a table, the contents of the table includes the following names

$names_to_remove = (invoke-sqlcmd -serverinstance "myserver" -database "mydb" -Query "select name from table_exception").names

$destination_server_names = (invoke-sqlcmd -serverinstance "myserver" -database "mydb" -Query "select name from table_names").names

Now, I want to compare the result of $names_to_remove with that of $destination_server_names

Let us assume that $destination_server_names consists of the following names

In that case, I only want to return names that are in $names_to_remove and also present in $destination_server_names. The idea is to return an array of such names and I can use it for further processing. Please note that the name matching should be case insensitive.

what should be returned is 3 names in an array where i can then loop

Answer 1

So, something like this?

# ignore this part. I don't like typing quotation marks!
$x = "Tom Rex Phil Alex Weston Bobby Michael"
$d = "Tom Rex Phil"

$names_to_remove = $x -split " "
$destination_server_names = $d -split " "

# start here
[array]$keepers = @()
$names_to_remove |
    ForEach-Object{
        if ($destination_server_names -contains $_){
            $keepers += $_
        }
    }
$keepers