checking tables data for equality

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 2005 Forums

Transact-SQL (2005)

checking tables data for equality

Author

Topic

ms65g
Constraint Violating Yak Guru

497 Posts

Posted - 2011-04-01 : 16:28:58

Consider two tables with data like these:
declare @t1 table(i int)
insert @t1 select 1 union select 2 union select 5

declare @t2 table(j int)
insert @t2 select 1 union select 3 union select 5

How can I find is two table same by a better query?

I use this for that:

select case when not exists (select * from @t1 t1 where not exists (select * from @t2 where j=t1.i)) and not exists (select * from @t2 t2 where not exists (select * from @t1 where i=t2.j)) then 'Equal' else 'Not Equal' end;

______________________

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-04-01 : 16:36:19

You can use the except keyword like this:

select * from @t1 except select * from @t2

If you reverse @t1 and @t2, of course you get the rows from @t2 that don't match rows in @t1

The intersect keyword can be similarly used to find rows that match.

If you have a key column that you can join on, binary_checksum would be another way to do this. That would just tell you whether the rows are identical or not.

ms65g
Constraint Violating Yak Guru

497 Posts

Posted - 2011-04-01 : 16:48:00

Yes,
your logic work.
--Your idea:
select case when not exists (select i from @t1 except select j from @t2)
and not exists (select j from @t2 except select i from @t1)
then 'Equal'
else 'Not Equal'
end;
--New idea:
select case when (select count(*) from @t1, @t2 where i=j) =
(select max(cnt) from (select count(*) from @t1 union select count(*) from @t2)d(cnt))
then 'Equal'
else 'Not Equal' end;

______________________

ms65g
Constraint Violating Yak Guru

497 Posts

Posted - 2011-04-01 : 17:02:52

What is your opinion about this?
First calculate position for values then multiply it to value then getting totals, if totals of two tables be same then two tables are equal.

select case when (select sum(rnk*i) from (select i, row_number() over(order by i) from @t1) as d(i, rnk)) =
(select sum(rnk*j) from (select j, row_number() over(order by j) from @t2) as d(j, rnk))
then 'Equal'
else 'Not Equal'
end;

______________________

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-04-01 : 18:59:30

quote:
Originally posted by ms65g

What is your opinion about this?
First calculate position for values then multiply it to value then getting totals, if totals of two tables be same then two tables are equal.

select case when (select sum(rnk*i) from (select i, row_number() over(order by i) from @t1) as d(i, rnk)) =
(select sum(rnk*j) from (select j, row_number() over(order by j) from @t2) as d(j, rnk))
then 'Equal'
else 'Not Equal'
end;

______________________

Looks like it should work - I can't be sure. Regardless, it has a few selects, multiplications and summations. Are you looking for this as an example for a larger problem with lots of columns and rows - if so this may not be very efficient.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources