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

MrFlinstone 531 Reputation points
2022-04-05T18:05:04.787+00:00

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

190267-image.png
$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 190238-image.png

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
190306-image.png

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,352 questions
Windows Server PowerShell
Windows Server PowerShell
Windows Server: A family of Microsoft server operating systems that support enterprise-level management, data storage, applications, and communications.PowerShell: A family of Microsoft task automation and configuration management frameworks consisting of a command-line shell and associated scripting language.
5,460 questions
0 comments No comments
{count} votes

Accepted answer
  1. Rich Matheisen 45,906 Reputation points
    2022-04-05T19:28:16.08+00:00

    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
    
    0 comments No comments

0 additional answers

Sort by: Most helpful