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 locationID
here.
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?
Best Answer
You can also do this with dynamic SQL without having to manually build out all the column names.
You can take the output of this query as is and store the query somewhere, or you can comment out the
SELECT
and uncomment theEXEC
and leave it as permanent dynamic SQL - in this case it will automatically adapt to column changes in the two tables.Another idea (assuming LocationID is unique) - and it occurred to me you may want to include the master row so you can quickly spot the columns that are different:
This version is a little cheaper (mostly by avoiding the
DISTINCT
against the master table, at the cost of needing to specify all of the columns one more time - which again you can automate as per above):However all of these options are poorer performers with worse plans than Rachel's simple
LEFT JOIN
. I tried to stick to the theme of usingEXCEPT
even though it is more about syntax than performance.The key takeaway is that if the column count is too high to deal with manually, you can use the dynamic SQL approach above to construct whatever query you want to use - and you can do that one time and store the result, or have the code generated every time. To generate Rachel's query using dynamic SQL, not much needs to change: