SQLTeam.com | Weblogs | Forums

Better way of comparing column values from two different tables which have same columns


#1

hi i am looking for code where i want to compare multiple columns which exist in 2 different tables, note that column names are same in both the tables,

i want to pull only modified columns and their values, please find below i have written to achieve the functionality

i am pulling some bit values thru xml data and i want to from those i want to update the entries in the database

what i am doing is i am comparing each and every column with following statement which does not look good to me

-- pulling existing data before save thru below query
select @oldoverid=IsOverride,@oldisvisible=IsShow,@oldishelp=IsShowHelp from (nolock) where id= 21999 and SetId=3433
--converting bit(0 or 1) to true/false for saving in oldvalue/freshvalue variables to be logged into adminactivity table
select
case when @oldisvisible=0 then 'False' else 'True' end ,
case when @oldishelp=0 then 'False' else 'True' end ,
case when @oldoverid=0 then 'False' else 'True' end

         -- comparing differnt properties of a element one by one , this is robust , variables starting with new are what i get from xml and want to update 
		 -- in database after comparing with oldvalues of specific columns
          if( @newvisible<> @oldvisible)  --this is how i am comparing now for different columns
           begin  
                                
				set @oldvalue = 'visible' + '-' +  case when @oldvisible=0 then 'False' else 'True' end
				set @freshvalue = 'visible' + '-' + case when  @newvisible=0 then 'False' else 'True' end
                select  @oldvalue oldvalue ,@freshvalue newvalue ,@freshvalue 'visiblein- modified'
           end
          
           if( @oldoverid <> @newoverride)
           begin
                set @oldvalue = @oldvalue + ',' + 'AllowOverride' + '-' + case when  @oldvisible=0 then 'False' else 'True' end
				set @freshvalue = @freshvalue + ',' + 'Allow Override' + ',' + case when @newvisible=0 then 'False' else 'True' end
                select  @oldvalue 'oldvalue',  @freshvalue 'newvalue'
           end              
           
end          

end


#2

It looks like someone may have done the work for this already:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=23054


#3

If you are trying to do a data compare (vs schema compare) then something like this will yield the recordset where at least one value has changed:select <column list> from MyTableNEW except select <column list> from MyTableORIGYou'll need to substitute your own list of column names in both SELECTs. What you do with the recordset is up to you.