compare data row by row - 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

Transact-SQL (2000)

compare data row by row

Author

Topic

rajani
Constraint Violating Yak Guru

367 Posts

Posted - 2004-06-07 : 17:17:18

Hi friends

we have a table which is specific to our application.we change data in this table every time we release new builds.
what i want to know is there any way compare 2 tables (which are same structure) row by row.for example

Table a:-
Col1 Col2
--- -----
1 'karl'
2 'gin'

Table b:-
Col1 Col2
--- -----
1 'karl'
2 'gin1'

Here i want 2nd row to be returned bcoz col2 value is different.i know i can do something like
select b.* from b,a where a.col1<>b.col1 and a.col2<>b.col2

but what if table's have 20 columns each?
is there any way better to compare or the only way is my abv example.
I even tried like
select * from b exists(select * from a)
but i'm not sure it is getting what i want??
Many thanks for ur ideas :)

Cheers

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-06-07 : 17:19:52

The way you are doing it is probably the best. It's easy to complain about 20 columns, but you only have to code it once. (grin)

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

rajani
Constraint Violating Yak Guru

367 Posts

Posted - 2004-06-07 : 17:21:16

Derrick
Thats true.but i was wondering if is there any other way.
Thanks

Cheers

kselvia
Aged Yak Warrior

526 Posts

Posted - 2004-06-07 : 17:38:14

You could do:

SELECT * FROM
(
SELECT CHECKSUM_AGG(BINARY_CHECKSUM(*)) table1_chk
FROM table1
) t1,
(
SELECT CHECKSUM_AGG(BINARY_CHECKSUM(*)) table2_chk
FROM table2
) t2
WHERE t1.table1_chk <> t2.table2_chk

to tell you if the tables are different. (You would still have to figure out which columns were different like you are doing already.)

rajani
Constraint Violating Yak Guru

367 Posts

Posted - 2004-06-07 : 17:43:00

Thanks for the post kselvia.
any idea what following query does
select * from b where not exists(select * from a)
here table b has 1500 records and table a has 700 records.only these 700 records that r matching between these 2 tables.but when i run abv query it returns all 1500 recods(i.e table b data) actually i was expecting to see only last 800 records only (1500-700).
any ideas plz

Cheers

kselvia
Aged Yak Warrior

526 Posts

Posted - 2004-06-07 : 17:45:18

You have to join tables b and a in the subquery.

something like;

select * from b where not exists (select 1 from a where a.id = b.id)

rajani
Constraint Violating Yak Guru

367 Posts

Posted - 2004-06-07 : 17:48:28

since i was not getting results i wanted i finally did what u suggested.
but my question remains..
select * from b where not exists (select 1 from a)
does not it supposed to get only non matching records automatically?? or i've misunderstanding abt this query (especially exists keyword)??
Thanks for ur quick replies

Cheers

kselvia
Aged Yak Warrior

526 Posts

Posted - 2004-06-07 : 17:51:48

For each row in b the correlated subquery (select 1 from a) is executed. (Although it is not correlated to anything in your case)

As soon as any row is returned, the exists condition for that row is true and the row from b is returned.

Since you are not relating the rows from a to the row from b, the query is always true.

The statement (select 1 from a) is no different than (select 1 , or select *) (assuming a has at least 1 row in it)

rajani
Constraint Violating Yak Guru

367 Posts

Posted - 2004-06-07 : 17:53:40

Thank you very muck kselvia.
Now that makes sense :-)

Cheers

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources