So, here's an example, assuming we are comparing tables A and B, and the
primary key of both tables is ID:

SELECT MIN(TableName) as TableName, ID, COL1, COL2, COL3 ...
FROM
(
SELECT 'Table A' as TableName, A.ID, A.COL1, A.COL2, A.COL3, ...
FROM A
UNION ALL
SELECT 'Table B' as TableName, B.ID, B.COL1, B.COl2, B.COL3, ...
FROM B
) tmp
GROUP BY ID, COL1, COL2, COL3 ...
HAVING COUNT(*) = 1
ORDER BY ID









Please take a few minutes to provide feedback on the quality of service you received from our staff. The Department of Education values your feedback as a customer. Commissioner of Education Jeanine Blomberg is committed to continuously assessing and improving the level and quality of services provided to you.Simply use the link below. Thank you in advance for completing the survey.


http://data.fldoe.org/cs/default.cfm?staff=Ed.[Email address protected]




-----Original Message-----
From: Vanessa van Gelder
[mailto:mssqldba-ezmlmshield-x43799643.[Email address protected]
Sent: Friday, August 31, 2007 8:24 AM
To: LazyDBA Discussion
Subject: comparing two tables

select * from srv.masson.dbo.coding a left join masson.dbo.coding b

on a.articlekey = b.articlekey

and a.systemkey = b.systemkey

where b.articlekey is null



this should bring back everything that's isn't present in table b
shouldn't
it?



argh



Vanessa Fredanus- van Gelder| SQL DBA | DXS Systems | Tel. +27 21 853
3317|
Fax. +27 21 854 7953

When I was young, I used to admire intelligent people; as I grow older,
I
admire kind people. Abraham Joshua Heschel





---------------------------------------------------------------------
TO REPLY TO EVERYBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
To post a dba job: http://jobs.lazydba.com
To subscribe : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html

MS Sql Server LazyDBA home page