check for differences between two rows

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 2005 Forums

Transact-SQL (2005)

check for differences between two rows

Author

Topic

Myles McKee
Starting Member

5 Posts

Posted - 2010-02-17 : 12:30:50

I would like to develop a generic process to check to see if there are any value differences between two rows in two different tables with the same column structure. Is there any easy way to do this in t-sql? For example...

select * from table1 t1
where exists (select * from table2 t2 where t1.id = t2.id)

... that checks if there are any value differences between the two rows for all other columns besides id.

Thanks.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-17 : 12:40:42

what do you mean by generic process?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Myles McKee
Starting Member

5 Posts

Posted - 2010-02-17 : 13:18:49

By generic, I mean I don't want to have to write code where I check to make sure every column matches

select * from table1 t1, table2 t2
where t1.first_name = t2.first_name
and t1.last_name = t2.last_name
... etc

Rather, I just want to check if row 1 and table1 exists in table2, regardless of the column structure so long as the structure matches between the two tables.

Thanks

Kristen
Test

22859 Posts

Posted - 2010-02-17 : 13:29:40

I used this:


SELECT TOP 100 
	'D', D.*, 
	CHAR(13)+CHAR(10)+'S   ', S.*, 
	CHAR(13)+CHAR(10)+'----------'
FROM	DestinationDB.dbo.[MyTable] AS D
	JOIN SourceDB.dbo.[MyTable] AS S
		 ON D.[MyPK] = S.[MyPK]

WHERE
(
	   (D.[MyNonCharCol] <> S.[MyNonCharCol] 
		   OR (D.[MyNonCharCol] IS NULL AND S.[MyNonCharCol] IS NOT NULL) 
		   OR (D.[MyNonCharCol] IS NOT NULL AND S.[MyNonCharCol] IS NULL))
	OR (D.[MyCharCol] COLLATE Latin1_General_BIN <> S.[MyCharCol] 
		   OR (D.[MyCharCol] IS NULL AND S.[MyCharCol] IS NOT NULL) 
		   OR (D.[MyCharCol] IS NOT NULL AND S.[MyCharCol] IS NULL))
	OR ...
)

This assumes TEXT output window, and thus the CHAR(13)+CHAR(10) puts the Source / Destination columns vertical above each other to easily see the differences.

MyNonCharCol is any column with a datatype that is NOT Char/Varchar variants
MyCharCol is any column that IS a Char/Varchar datatype

COLLATE Latin1_General_BIN is to get case-sensitive comparison of Strings - in case only difference is capitalisation

Myles McKee
Starting Member

5 Posts

Posted - 2010-02-17 : 13:46:36

Kristen - thanks for sharing your code.

I'm not sure I understand the code. Are the parameters [MyNonCharCol] and [MyCharCol] placeholders that I would have to replace with the actual table column names in the code? If so, then it's not exactly what I'm looking for. I'm looking for a way to see if two rows are the same without actually having to compare each column by code (or by dynamic loop, for that matter). I assumed that I could accomplish it using an EXISTS statement, but I wasn't (I don't ever use EXISTS statements). Maybe this capability doesn't exist in T-SQL, or maybe I don't fully understand your code.

Thanks again.

Kristen
Test

22859 Posts

Posted - 2010-02-17 : 14:18:19

" Are the parameters [MyNonCharCol] and [MyCharCol] placeholders that I would have to replace with the actual table column names in the code?"

Yes

"I'm looking for a way to see if two rows are the same without actually having to compare each column by code "

Only rows which are different in, at least, one column will be included.

(Which I think is what you want?)

Myles McKee
Starting Member

5 Posts

Posted - 2010-02-17 : 14:25:11

"(Which I think is what you want?)"
Yes, but I don't know which rows will be different. I have tables that have hundreds of columns, the structure of which is frequently modified. I'm trying to develop a BEFORE UPDATE trigger to store historical records for tables before any updates occur. Often times updates occur without any changes actually occuring to cell values. My process would become much simpler if I can verify whether or not any values have changed.

Thanks.

Kristen
Test

22859 Posts

Posted - 2010-02-17 : 14:47:15

The WHERE clause above (repeated for all relevant columns in the table) will establish if one (or many) columns have changed.

That statement can be generated "mechanically" (using the system tables)

Myles McKee
Starting Member

5 Posts

Posted - 2010-02-17 : 15:45:39

Thanks for your help Kristen. I'll proceed by developing a function using the information schema table to compare two tables.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources