I'm comparing a bunch of tables from different databases on different servers to a Master record. I need to know which servers, identified by
locationID, have the non-matching rows because they might need maintenance.
I've got a simple
EXCEPT query where I compare a table where each row is the configuration from each server;
table1 has one row per server with all configuration plus
locationID which is a column that tells me which server it is. I compare these all to a
table1_master table which has the right settings, but I exclude the
locationID since it won't match.
Simple query below:
SELECT everything, but, locationID FROM table1 EXCEPT SELECT everything, but, locationID FROM table1_master
There's only one master row I compare all servers to, and I don't select it's
This is an example of the rows I'm comparing. Each has a primary key, a single column
varchar and a giant list of that's dozens of columns. I want to compare all columns except LocationID, but I need LocationID to identify the rows.
LocationID setting setting setting setting CS02 C Y Y Y Y CS03 C Y Y Y Y CS06 C Y N Y Y
In this example say CS02 is my Master record, so since all settings are the same in CS02 and CS03, those rows don't show up, but CS06's does. But in my
EXCEPT query, I'm not actually catching LocationID so I don't actually know which row was returned.
This returns the rows I need but NOT the
locationID, so I don't know which rows are wrong. Is there any way I can include
locationID in the results set while kicking out the matching rows?
The solution I thought of was to make a row for each server in the
table1_master table, so each
locationID is represented, but they all have the same data other than that. My
EXCLUDE query should then return the
locationID and my info, but is that the best way to do it?