Compare 2 tables in SQL Server

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 2 tables in SQL Server

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2003-12-04 : 08:01:11

Bhuvana writes "How do I compare 2 tables in SQL Server?
Is there a system proc by the name sp_compare_tables?
I dont find the same .Where could I get the script for the same?"

Amethystium
Aged Yak Warrior

701 Posts

Posted - 2003-12-04 : 08:35:19

What are you trying to compare? Contents? Structure?

________________
Make love not war!

dsdeming

479 Posts

Posted - 2003-12-04 : 08:38:57

You can use the CHECKSUM function to compare tables:

set nocount on

create table a( a int )
insert into a select 1
insert into a select 2
insert into a select 3

create table b( a int )
insert into b select 1
insert into b select 2
insert into b select 4

select *
from a
group by
a
having checksum( a ) not in( select checksum( a ) from b )

select *
from b
group by
a
having checksum( a ) not in( select checksum( a ) from a )

drop table a
drop table b

Dennis

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-12-04 : 11:49:34

Use the union/group by technique -- it's the easiest and most accurate. Checksum's are good for estimates, but they may not be entirely accurate.


Select Min(TableName) as table, col1, col2, ....
from
 (select 'Table1' as TableName, col1, col2,  .....
  from Table1
  UNION ALL
  select 'Table2' as TableName, col1, col2, .....
  From Table2
  ) a
GROUP BY col1, col2, .....
HAVING COUNT(*) = 1

That returns all rows in EITHER table that don't have a match in the other table. List out all columns to compare in both tables.

- Jeff

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources