CODEWITHSUNDEEP
sqlsql-serverdatabasereportrelational-database
Erik
Erik

Reputation: 61

SQL Server: Listing Differences Between Tables

There are a few similar threads over the years regarding this but I haven't found or been able to do what I'm seeking.

I currently have two tables that have the same schema that I produce through a script. For name's sake, one is "results_prior" and the other is "results_current". This new query will ideally run once a month and find if there were any differences. Example:

TABLE: results_prior 

----------------------------------------
| ID | ENVIRONMENT | EDITION | CONTACT |
----------------------------------------
| 03 | Development | 2008    | Bob     |
----------------------------------------
| 05 | Production  | 2012    | Phil    |
----------------------------------------
| 09 | Development | 2008    | Erik    |
----------------------------------------
| 13 | Production  | 2012    | Ashley  |
----------------------------------------
| 22 | Production  | 2012    | Erik    |
----------------------------------------

TABLE: results_current 

----------------------------------------
| ID | ENVIRONMENT | EDITION | CONTACT |
----------------------------------------
| 03 | Development | 2008    | Bob     |
----------------------------------------
| 05 | Production  | 2012    | Phil    |
----------------------------------------
| 22 | Production  | 2012    | Erik    |
----------------------------------------

When the two are compared, the result should be:

----------------------------------------
| ID | ENVIRONMENT | EDITION | CONTACT |
----------------------------------------
| 09 | Development | 2008    | Erik    |
----------------------------------------
| 13 | Production  | 2012    | Ashley  |
----------------------------------------

Since 09 and 13 were in results_current. Likewise, and this is perhaps the tricky part, is having the same be done if results_current has more results than results_prior. So, vice-versa.

Sorry I have no example code to go off of. I have been goofing with UNION, JOINs, and EXCEPTs the last few hours and I feel like my logic (again, in SQL Server) is just not making any sense. Any assistance would be appreciated.

Upvotes: 1

Views: 60

Answers (2)

JustAPup
JustAPup

Reputation: 1780

I would try something roughly similar to this:

SELECT ID, ENVIRONMENT, EDITION, CONTACT FROM RESULTS_PRIOR
WHERE ID NOT IN (SELECT ID FROM RESULTS_CURRENT)
UNION
SELECT ID, ENVIRONMENT, EDITION, CONTACT FROM RESULTS_CURRENT
WHERE ID NOT IN (SELECT ID FROM RESULTS_PRIOR)

Upvotes: 0

Vamsi Prabhala
Vamsi Prabhala

Reputation: 49270

This can be treated as the result set of A-B union B-A. I assume all the columns are the same in both the tables.Hence the * in select.

(select * from results_prior 
except
select * from results_current)
union all
(select * from results_current 
except
select * from results_prior)

Upvotes: 3

Related Questions