Best way to accomplish complete row compares

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

Transact-SQL (2000)

Best way to accomplish complete row compares

Author

Topic

Jusvistin
Yak Posting Veteran

81 Posts

Posted - 2003-08-07 : 14:58:21

Ok,

This will be last post for today, I'd hate to piss anybody off :-)

My background is in analysis and as a business matter expert, not a programmer - just so you know.

At my old company we used a series of COBOL programs to generate flat files that we would send to various vendors. Each week we would extract current information about each employee, apply a bunch of business rules, and then produce a "current state" flat file that contained Vendor determined data not stored in any of our company databases.

We would then compare the "current state" file against last week's file, identify changes, and then format a change file to be sent to our vendors.

Easy enough.

Assuming I were to mimic that process using SQL, I'm guessing I'd have 2 tables, one for each week. Then to determine if any data had changed, I'd probably need to compare each field for every row.

So... Here's my question....

If I've got two tables, and want to determine what fields have changed for a particular record, will I have to do a complicated where statement such as

where
t1.field1 <> t1.field1 and
t2.field2 <> t2.field2

etc etc.

Or is there a way to compares of complete rows ?

Thanks,
Kevin

setbasedisthetruepath
Used SQL Salesman

992 Posts

Posted - 2003-08-07 : 15:47:34

Take a look at row checksums in BOL, it might be an easy way for you to avoid writing lengthy DML to compare column after column.

Jonathan
{0}

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-08-07 : 15:55:33

the "Mr Cross Join" method:



select min(TableName) as TableName, col1, col2, ...
from
(
select 'table1' as tableName, col1, col2, col3 ....
from table1
union all
select 'table2' as tableName, col1, col2, col3 ....
from table2
)
a
group by col1, col2, col3, ....
having count(*)=1

returns all rows from either table that are different or don't exist in the other.

Joins and where clauses can be pains because of Nulls, and this is generally much easier to write. I also have a stored proc somewhere in the "Script library" forum that compares two tables or views for you in a similiar manner.

- Jeff

Jusvistin
Yak Posting Veteran

81 Posts

Posted - 2003-08-07 : 20:00:04

Thanks, I'll check these out.

Kevin

dsdeming

479 Posts

Posted - 2003-08-08 : 07:58:04

CHECKSUM has always worked for me, and as Jonathan says, it saves writing some lengthy code.

Dennis

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2003-08-08 : 08:44:31

However,

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=26492

nr
SQLTeam MVY

12543 Posts

Posted - 2003-08-08 : 09:37:12

checksum returns an int so can't be used reliably for equalities like this (unless you have only the number of values that can be stored in an int or less).
Could be used to distinguish 'not equal' and 'maybe equal' but not 'not equal' and 'equal'

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-08-08 : 10:01:39

Well put, Nigel....

If you use a checksum, it is important to know that:

"Jeff"

and

"I love SQL Team they are great people"

might BOTH potentially return the same checksum ....

but

"Jeff"

and

"Jeff"

will DEFINITELY return the same checksum. and that's all you can really count on.

- Jeff

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources