How to Compare two SQL tables and get the difference value using Powershell

Question

I have two tables in a SQL Server database.

Table1 looks like:

ServerName   ServiceName ServiceStatus
----------   ----------- -------------
Server1      iis         running      
Server2      sql         running      
Server3      www         running 

and Table2 looks like:

ServerName   ServiceName ServiceStatus
----------   ----------- -------------
Server1      iis         running      
Server2      sql         Notrunning      
Server3      www         running 

I want to compare the tables using Powershell and get the difference in status as {On Server2 service "sql" status is changed from "running" to "Notrunning"}.

I have tried below, but go no output. Please help me.

$SQLServer = "Server1\sql"
$db3 = "test"
$T1data = "SELECT ServerName, ServiceName, ServiceStatus FROM Table1"
$T2data = "SELECT ServerName, ServiceName, ServiceStatus FROM Table2"
$s = Invoke-Sqlcmd -ServerInstance $SQLServer -Database $db3 -Query $T1data 
$p = Invoke-Sqlcmd -ServerInstance $SQLServer -Database $db3 -Query $T2data 
#$q = Compare-Object $p $s -Property servicestatus

Foreach($q in $s.serverName) {
    while ($s.serverName -eq $p.serverName -and $s.servicename -eq $p.servicename ) {
        if($s.servicestatus -ne $p.servicestatus) {
              Write-Output  " On server $s.serverName the service $s.servicename changed from $P.servicestatus to $s.servicestatus " 
        }
    }
}

Answer 1

based on this reading on mssqltips website:

by dumping out the table data into files via the BCP utility and then getting MD5 hash values of the files for comparison. The data files format are controlled by -ValueOnly parameter, if using -ValueOnly parameter, we will use -c parameter for BCP.exe (all columns are treated as character data type), otherwise, we use -n parameter (all columns are treated as native data type) for BCP.exe.

Answer 2

What about a different technique, and use SQL server to provide the differences for you?

SELECT t1.ServerName, t1.ServiceName, t1.ServiceStatus 
FROM Table1 t1
JOIN Table2 t2 on t1.SeverName = t2.ServerName
AND t1.ServiceName = t2.ServiceName 
AND t1.ServiceStatus <> t2.ServiceStatus