On 11 Jan 2005 22:34:27 -0800, AzGhanv/. wrote:
I m searching for some Script / Function ... to find difference in data
b/w 2 similar tables (exactly same fields structure) in sql 2000.
plz update me asap !
Hi AzGhanv/.,
SELECT CASE
WHEN a.KeyCol = 1 THEN 'FirstTable'
ELSE 'SecondTable'
END AS Origin,
COALESCE (a.KeyCol1, b.KeyCol1) AS KeyCol1,
COALESCE (a.KeyCol2, b.KeyCol2) AS KeyCol2,
.....
COALESCE (a.KeyColN, b.KeyColN) AS KeyColN,
COALESCE (a.DataCol1, b.DataCol1) AS DataCol1,
COALESCE (a.DataCol2, b.DataCol2) AS DataCol2,
.....
COALESCE (a.DataColN, b.DataColN) AS DataColN
FROM FirstTable AS a
FULL OUTER JOIN SecondTable AS b
ON a.KeyCol1 = b.KeyCol1
AND a.KeyCol2 = b.KeyCol2
.....
AND a.KeyColN = b.KeyColN
AND a.DataCol1 = b.DataCol1
AND a.DataCol2 = b.DataCol2
.....
AND a.DataColN = b.DataColN
NOTE: The above assumes that no column may contain NULLS. For each data
column that allow NULLS, you'll have to replace
AND a.DataColX = b.DataColX
with
AND (a.DataColX = b.DataColX
OR (a.DataColX IS NULL AND b.DataColX IS NULL))
or, alternatively
AND NULLIF (a.DataColX, b.DataColX) IS NULL
AND NULLIF (b.DataColX, a.DataColX) IS NULL
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)