SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
 Password:
Save Password
Forgot your Password?

 All Forums
 Old Forums
 CLOSED - General SQL Server
 Compare 2 tables		  Forum Locked
 Printer Friendly
Author Previous Topic Topic Next Topic  

Eithne
Starting Member

8 Posts
Posted - 11/10/2004 :  06:17:54  Show Profile
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

United Kingdom
22191 Posts
Posted - 11/10/2004 :  07:04:50  Show Profile
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
Edited by - Kristen on 10/31/2005 13:46:45
Go to Top of Page

tuenty
Constraint Violating Yak Guru

USA
278 Posts
Posted - 11/10/2004 :  08:03:17  Show Profile 
Select * from tblA where not exists
(
    Select 1 from tblB
    Where tblA.PK=tblB.Pk
)
to find records in tblA that does not exists on tblB

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

A candle loses nothing by lighting another candle
Go to Top of Page

jsmith8858
Dr. Cross Join

USA
7423 Posts
Posted - 11/10/2004 :  09:30:15  Show Profile  Visit jsmith8858's Homepage
also check out:

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

- Jeff
Go to Top of Page

rockmoose
SQL Natt Alfen

Sweden
3279 Posts
Posted - 11/10/2004 :  19:07:49  Show Profile
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
Go to Top of Page

golum1
Starting Member

2 Posts
Posted - 03/17/2006 :  13:26:04  Show Profile
http://www.getfirefly.net/
Go to Top of Page

jsmith8858
Dr. Cross Join

USA
7423 Posts
Posted - 03/17/2006 :  13:40:57  Show Profile  Visit jsmith8858's Homepage
golum1 -- I'm sure if you want to advertise at SQLTeam, Graz will be happy to quote you a fair price.
Go to Top of Page

jhermiz
Flowing Fount of Yak Knowledge

USA
3564 Posts
Posted - 03/17/2006 :  14:30:13  Show Profile  Visit jhermiz's Homepage
Dont think he's advertising, firefly is free, and it definately goes with the subject of this topic.

Keeping the web experience alive -- http://www.web-impulse.com

RS Blog -- http://weblogs.sqlteam.com/jhermiz
Go to Top of Page
  Previous Topic Topic Next Topic  
 Forum Locked
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000