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  SQL Server Development (2000)  Comparing two records

Author  Topic  mk1matt Starting Member 7 Posts Posted - 2010-01-27 : 06:06:28 Hopefully I'll explain this correctly. I need to produce a report which compares two records and produces a listing of columns where there is a differece. I've written a routine which produces a series of records (in a temporary table) containing a pair of primary keys (the keys of the records to be compared) to the main ratings table. The bit I'm stuck with, is working out the differences and outputting the results. The input table looks something like this: #rating_list ------------ bp_id|rejected_rating|approved_rating 21141|71901|68327 18053|71880|71724 11332|72268|68102 3756|72344|60892 The ratings table looks like this (but much bigger): b2_risk_rating_inst_prop ------------------------ rating_id|bp_category_rd|cashflow_adverse_case_score|bp_experience_yn 71901|1003|75|1 68327|1004|75|1 71880|1002|62|0 71724|1002|62|0 72268|1003|15|1 68102|1003|15|0 72344|1001|57|1 60892|1001|56|1 The output needs to look something like this, showing only the columns where there is a difference: #Output ------- bp_id|field_name 21141|bp_category_rd 11332|bp_experience_yn 3756|cashflow_adverse_case_score 3756|bp_experience_yn Now, the b2_risk_rating_inst_prop table has 77 columns, so the query could be quite large. I was considering using a cursor (Noooooooooo!!!!!!) to loop through syscolumns for b2_risk_rating_inst_prop and write dynamic SQL to test each column. I'm still struggling with the concept though. Any thoughts on how to go about doing this? Matt