By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,806 Members | 1,829 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,806 IT Pros & Developers. It's quick & easy.

Script / Function ... to find difference b/w 2 similar tables

P: n/a
Hi,

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 !

thanks in advance !

Jul 23 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
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)
Jul 23 '05 #2

P: n/a
Assuming by differences you mean rows in A not in B as well as vice
versa, a good solution would be
create table t1 (a int,b int)
create table t2 (a int,b int)

insert into t1 values (1,2)
insert into t1 values(3,4)
insert into t1 values(5,6)
insert into t2 values(1,2)
insert into t2 values(3,7)
insert into t2 values(5,6)
select sum(t) ,a,b from

(select 1 as t,* from t1
union all
select 2 as t,* from t2
) as both
group by a,b
this way you see both kinds of differences as 1 or 2, and 3 means no
difference (you can add having sum(t)<3 not to see them)
hope this helps
Tzvika

Jul 23 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.