Compare 2 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

Old Forums

CLOSED - General SQL Server

Compare 2 tables

Author

Topic

Eithne
Starting Member

8 Posts

Posted - 2004-11-10 : 06:17:54

Is there any way i can compare the records between 2 tables (table a and table b where order id is the same in both) and tell what the difference is between the 2 of them.

Kristen
Test

22859 Posts

Posted - 2004-11-10 : 07:04:50

http://www.red-gate.com/
or
use Google to search for a COMPARE script by Viktor Gorodnichenko (it may be on SQL Team too)

EDIT: It can be found here: http://www.sqlteam.com/Forums/topic.asp?TOPIC_ID=27234

Kristen

tuenty
Constraint Violating Yak Guru

278 Posts

Posted - 2004-11-10 : 08:03:17

[code]Select * from tblA where not exists
(
Select 1 from tblB
Where tblA.PK=tblB.Pk
)[/code]to find records in tblA that does not exists on tblB

*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*

A candle loses nothing by lighting another candle

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-11-10 : 09:30:15

also check out:

http://weblogs.sqlteam.com/jeffs/archive/2004/11/10/2737.aspx

- Jeff

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2004-11-10 : 19:07:49

binary_checksum can in some case give same checksum, see bol.

This will give records that are
1. different
2. in table a but not in table b, and vice versa

select
	a.[order id],
        a.col1, a.col2,,,,
	b.[order id],
        b.col1, b.col2,,,,
from
	( select *, binary_checksum(*) as bchk from a ) as a
	full join
	( select *, binary_checksum(*) as bchk from b ) as b
		on a.[order id] = b.[order id]
where
	a.bchk <> b.bchk
	or a.bchk + b.bchk is null

rockmoose

golum1
Starting Member

2 Posts

Posted - 2006-03-17 : 13:26:04

http://www.getfirefly.net/

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2006-03-17 : 13:40:57

golum1 -- I'm sure if you want to advertise at SQLTeam, Graz will be happy to quote you a fair price.

jhermiz

3564 Posts

Posted - 2006-03-17 : 14:30:13