• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 396
  • Last Modified:

Table data comparison script

I'm looking for a script that will allow me to compare two identical tables lets call the table A and table B in different databases.  Table A can be the master table.

1. The script ideally must shows rows where additional rows exists A which are not in B.
2. Where additional rows exist in B but not in A.
3. Also matching on a primary key it must show rows where values in any columns differ between rows.
0
dfincham28
Asked:
dfincham28
3 Solutions
 
dreadyCommented:
1:
select * from tblA where tblA.Id not in (select id from  tblB)
2:
select * from tblB where tblB.id not in (select id from tblA)
3. Depends on how many columns you have... lets say that except for the PK column (ID) you hava colA, colB and colC

select * from tblA inner join tblB on tblA.ID = tblB.ID where tblA.colA <> tblB.ColA or tblA.colB <> tblB.ColB or tblA.colC <> tblB.colC
0
 
ZberteocCommented:
Lets say your tables have the following structure:

id - PK,
col1,
col2,
...
colN
select 
	*
from 
	db1..tableA as A
	full join db2..tableB as B
		on B.id=A.id
where
	A.id is null
	or 
	B.id is null
	or 
	( 
		A.id=B.id 
		and 
		(
			isnull(A.col1,<def_val_col1_type>)<>isnull(B.col1,<def_val_col1_type>)
			or
			isnull(A.col2,<def_val_col2_type>)<>isnull(B.col2,<def_val_col2_type>)
			or
				...
			isnull(A.colN,<def_val_colN_type>)<>isnull(B.colN,<def_val_colN_type>)
		)
	)

Open in new window

0
 
Éric MoreauSenior .Net ConsultantCommented:
scripts are good until you find professional tools like http://www.red-gate.com/products/SQL_Data_Compare/index.htm
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
dfincham28Author Commented:
The problem with professional tools is whenever you need to do the data comparison be it at a client site you will need to have that tool installed?
0
 
dfincham28Author Commented:
SQL Delta is a better professional tool
0
 
dfincham28Author Commented:
Someone at work has solved the problem for me
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now