My usual method for comparing data sets from SQL databases is just simply do an outer join between the two. For procs, that means inserting them into temp tables and then joining the temp tables.
For example:
create table #T1 (
CountryName varchar(100) primary key,
ISO2 char(2),
ISO3 char(3),
ContinentName varchar(100));
insert into #T1 (CountryName, ISO2, ISO3, ContinentName)
exec dbo.MyProc1;
create table #T2 (
CountryName varchar(100) primary key,
ISO2 char(2),
ISO3 char(3),
ContinentName varchar(100));
insert into #T2 (CountryName, ISO2, ISO3, ContinentName)
exec dbo.MyProc2;
select *
from #T1 t1
full outer join #T2 t2
on t1.CountryName = t2.CountryName
and t1.ISO2 = t2.ISO2
and t1.ISO3 = t2.ISO3
and t1.ContinentName = t2.ContinentName
where t2.CountryName is null
or t1.CountryName is null;
That will give you a full set of discrepancies between the two record sets.
If you want to change the comparison, change the join. For example, you might want to just know which countries have exact matches, but where the continent is different. So change the join to:
on t1.CountryName = t2.CountryName
and t1.ContinentName != t2.ContinentName
(And get rid of the where clause.)
You'll immediately have those differences. And so on, whatever comparisons you want to do.
Make a change to one of the procs, truncate the table its results are in, rerun the insert statement, and rerun your comparisons, till you get matching result sets.
If the result sets are large enough and the comparisons are complex enough, you can even add indexes to the temp tables to make the queries faster.
Also, if it matters, this doesn't run into the max rows issues that Excel has (though 2007 vastly increased the number of rows allowed).
Not saying this method is better, just saying it's what I use.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon