Compare two rows

Question

Compare two rows

cdong

Valued Member

Points: 64
More actions
October 19, 2004 at 8:42 am

#61167

Looking for easy ways to compare two rows?

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

john-132685 SSCertifiable Points: 6134 More actions · Answer 1

You need to get familiar with the system tables within SQL server. then you can easily create utility procs to do things like this for you:

DECLARE @collist VARCHAR(3000)

select @collist = ISNULL( @collist + CHAR(13) + CHAR(10) + ',' , '' ) + 'CASE WHEN a.' + c.name + ' <> b.' + c.name + ' THEN ''Y'' ELSE '' '' END AS ' + c.name

from syscolumns c

JOIN sysobjects o on o.id = c.id

where o.name = 'sysobjects'

ORDER BY colid

SELECT @collist = 'SELECT ' + @collist + ' FROM sysobjects a JOIN sysobjects b ON a.id = 1 and b.id = 2'

--SELECT @collist

PRINT 'COLUMNS THAT ARE DIFFERENT Between sysobjects id 1 and sysobjects id 2: '

EXEC ( @collist )

Steve Jones - SSC Editor SSC Guru Points: 738632 More actions · Answer 2

Data Compare from Red Gate. Cheap, easy and works well.

http://www.red-gate.com/SQL_Data_Compare.htm

Jeff Burton SSCommitted Points: 1551 More actions · Answer 3

REDGate SQL Data Compare is great, but if you are budget constrained and can't procure that use the built-in BINARY_CHECKSUM function:

DECLARE @BC1 INT, @BC2 INT

SELECT @BC1=BINARY_CHECKSUM(*) FROM <table> WHERE <clause to get 1st row>

SELECT @BC2=BINARY_CHECKSUM(*) FROM <table> WHERE <clause to get 2nd row>

IF @BC1 <> @BC2 PRINT '2 rows are different'

Jeff