Comparing Tables in SQL Server 2k

I have two tables that share (supposedly) 2 fields (PartID and RaceID) and those two tables should be identical as far as those two fields are concerned. That is, there should be the same number of rows in both tables and if listed in the same sort order in reference to these two fields, they should be identical. The problem is, they are not. There are in excess of 3000 records in each field and I need to write a query that will allow me to compare them row-by-row.

I am using sql server 2000 and I am (kind of)familiar with the SQL Query Analyzer. What I really need to know is how to write the select statement that will allow me to compare the two tables line-by-line to find the discrepancies or, better yet, simply show the discrepancies so I can focus on them.

Thanks!assuming that both columns are part of the primary key, i.e. not null

rows in table1 that don't exist in table2 --
select t1.PartID
, t1.RaceID
from table1 as t1
left outer
join table2 as t2
on t1.PartID = t2.PartID
and t1.RaceID = t2.RaceID
where t2.PartID is null

rows in table2 that don't exist in table1 --
select t2.PartID
, t2.RaceID
from table1 as t1
right outer
join table2 as t2
on t1.PartID = t2.PartID
and t1.RaceID = t2.RaceID
where t1.PartID is null|||That's okay, but I think it needs more cowbell:

select table1.PartID,
table1.RaceID,
table2.PartID,
table2.RaceID
from table1
full outer join table2
on table1.PartID = table2.PartID
and table1.RaceID = table2.RaceID
where table1.PartID is null
or table2.PartID is null

I just gotta have more cowbell!|||cowbell? is that unique to sql server? i never heard of it|||Thanks. That allowed me to find the discrepancies. Does anyone know where I can find a tutorial on joins (inner, outer, right, left, etc)?

Thanks!|||BOL (Books online), the topic "Types of Joins".|||thanks for your help!|||Cowbell:

http://209.151.80.80/media/cowbell.wmv|||hilarious clip

i don't understand the relevance to full outer joins, but that could just be me

:)|||http://www.urbandictionary.com/define.php?term=More+Cowbell&r=s&pos=1

Also: The addition of something which contributes nothing of value. ie: the full outer join as opposed to the separate left joins.|||What about creating a UNION view consisting only of the two key attributes? SQL Server will prepare a nice QEP for quick selection; if you have a second view, that queries on NULL values for both fields in that view it will be faster than any outer join, maybe ;-)|||I got a fever! And the only prescription is MORE COWBELL!|||a UNION view!! why didn't i think of that!!

select PartID
, RaceID
, min(source) as source_table
from (
select PartID
, RaceID
, 'table1' as source
from table1
union all
select PartID
, RaceID
, 'table2'
from table2
) as u
group
by PartID
, RaceID
having count(*) between 0 and 1
note how MIN() will tell you which of the tables the unmatched row comes from

sweet, eh?

the HAVING clause won't ever actually find count(*)=0, i just threw that in there because of the cowbells

:) :) :)|||And I thought I knew a little something about sql queries....

I guess that is accurate- a LITTLE something...

This is a very informational and helpful thread!

Thanks to all--although I am still a little in the dark re: this cowbell thing...