Compare Data in Tables - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Compare Data in Tables

Author

Topic

vnarula99
Starting Member

6 Posts

Posted - 2002-02-04 : 21:05:58

Hi,
I am in a problem, I have to compare two tables and find out which records are different from both the tables.
I am trying to find out the fastest method for getting the results.
Any help??

Vishal

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2002-02-04 : 21:13:49

Hi

Probably the easiest way is download SQL Data Compare from www.red-gate.com

Either that, or do an outer join between the two linked tables, with every column as a condition, but that sounds nasty.

Damian

vnarula99
Starting Member

6 Posts

Posted - 2002-02-04 : 21:24:21

Using a third party tool not an option. I have thought of using the outer join but some of my tables has as many as 25 columns.

Well anyway thanks for the reply
-Vishal

Vishal

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2002-02-04 : 21:34:26

Why isn't it an option ?

Damian

vnarula99
Starting Member

6 Posts

Posted - 2002-02-05 : 19:13:12

I just found out Data Compare(Red-Gate.com) only works for SQL server 7.0
I am using sql 6.5

Vishal

rrb
SQLTeam Poet Laureate

1479 Posts

Posted - 2002-02-05 : 21:07:38

Depending on how often you're doing it, I can't see why you can't do:

Select a.col1
from
a left join b
on a.col1 = b.col1
and a.col2 = b.col2
and a.col3 = b.col3
where
b.col1 is null

even if you've got 25 columns it'll only take a couple of minutes to generate the code...is there some complicating factor?

--
I hope that when I die someone will say of me "That guy sure owed me a lot of money"

rrb
SQLTeam Poet Laureate

1479 Posts

Posted - 2002-02-05 : 21:15:02

quote:

even if you've got 25 columns it'll only take a couple of minutes to generate the code...is there some complicating factor?

... and you could always do

select 'a.' + c.name + ' = b.' + c.name + ' and '
from syscolumns c inner join sysobjects o
on c.id = o.id
and o.name = 'a'

to generate the join bits (don't forget to prune the last "and")

--
I hope that when I die someone will say of me "That guy sure owed me a lot of money"

dsdeming

479 Posts

Posted - 2002-02-07 : 13:27:53

You could also try using the CHECKSUM or CHECKSUM_AGG functions.

vnarula99
Starting Member

6 Posts

Posted - 2002-02-07 : 18:44:04

Hi rrb

If i am not wrong the the outer joins will only give me the records which are present in one table and not in other, my aim is to find the differnce in two tables even if they both contain the same data.

Thanks for the reply

Vishal

rrb
SQLTeam Poet Laureate

1479 Posts

Posted - 2002-02-07 : 19:02:17

that's a terribly good point

- sorry I was assuming they had the same rows and number of rows but with differences in their values.

I assume then that you must have an id field to match on?? Otherwise how are you going to match them? It sounds like you're trying to handle replication without a GUID?

can you send me the tabledefs for you two tables and a simple example of what you want returned?

otherwise - all I can see is

select a.*, b.* from a left join b
on a.col1 = b.col1
and a.col2 = b.col2
and a.col3 = b.col3
where
b.col1 is null
union
select a.*, b.* from b left join a
on b.col1 = a.col1
and b.col2 = a.col2
and b.col3 = a.col3
where
a.col1 is null
and that's pretty useless without some kind of id field to match on...
--
I hope that when I die someone will say of me "That guy sure owed me a lot of money"

Edited by - rrb on 02/07/2002 19:04:24

vnarula99
Starting Member

6 Posts

Posted - 2002-02-07 : 19:55:48

Hi rrb

Below is the example and the desired results that I am looking for

Table A
Id1 Id2 Value1 Value2
1 1 aaa bbb
2 2 ccc ddd
3 3 eee fff
4 4 ggg hhh

Table B
Id1 Id2 Value1 Value2
1 1 aaa bbb
2 2 ddd ddd
5 5 iii jjj

Desired results
Id1 Id2 Value1 Value2
2 2 ccc ddd (different Value record from Table A)
2 2 ddd ddd (different Value record from Table A)
3 3 eee fff (Not Present in Table B)
4 4 ggg hhh (Not Present in Table B)
5 5 iii jjj (Not Present in Table A)

Thanks

Vishal

vnarula99
Starting Member

6 Posts

Posted - 2002-02-07 : 20:01:46

Hi rrb

I am posting the same reply as my earliar one.

Below is the example and the desired results that I am looking for

 
Table A
Id1 Id2 Value1 Value2
1    1   aaa   bbb
2    2   ccc   ddd
3    3   eee   fff
4    4   ggg   hhh

Table B
Id1 Id2 Value1 Value2
1    1   aaa    bbb
2    2   ddd    ddd
5    5   iii    jjj

Desired results
Id1 Id2 Value1 Value2
2    2   ccc    ddd (different Value record from Table A)
2    2   ddd    ddd (different Value record from Table A)
3    3   eee    fff (Not Present in Table B)
4    4   ggg    hhh (Not Present in Table B)
5    5   iii    jjj (Not Present in Table A)

Thanks

Vishal

rrb
SQLTeam Poet Laureate

1479 Posts

Posted - 2002-02-08 : 01:04:57

OK Vishal here you go. Someone like nr might be able to do it easier....

select a.*, 'not present in table b' as reason
into #a
from a left join b
on a.id1 = b.id1 and
a.id2 = b.id2 and
a.value1 = b.value1 and
a.value2 = b.value2
where
b.id1 is null

insert into #a
select b.*, 'not present in table a' as reason
from b left join a
on a.id1 = b.id1 and
a.id2 = b.id2 and
a.value1 = b.value1 and
a.value2 = b.value2
where
a.id1 is null

order by a.id1

update #a
Set reason = 'differs from table ' + substring(reason, 22,1)
from #a a
where id1 in (select id1 from
(select id1, count(id1) as countid1 from #a group by id1 having count(id1) > 1) a )

select * from #a
order by id1

drop table #a

and remember to use the
select 'a.' + c.name + ' = b.' + c.name + ' and '
from syscolumns c inner join sysobjects o
on c.id = o.id
and o.name = 'a'
to create your join "on" conditions....

Enjoy!

--
I hope that when I die someone will say of me "That guy sure owed me a lot of money"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources