select case when t.NotSubjectToDeductible <> t1.NotSubjectToDeductible then 'NotSubjectToDeductible' when t.DeductibleNotes <> t1.DeductibleNotes then 'DeductibleNotes' when t.NotSubjectToOOPLimit <> t1.NotSubjectToOOPLimit then 'NotSubjectToOOPLimit' else 'na' end as [Column],

case when t.NotSubjectToDeductible <> t1.NotSubjectToDeductible then t.NotSubjectToDeductible when t.DeductibleNotes <> t1.DeductibleNotes then t.DeductibleNotes when t.NotSubjectToOOPLimit <> t1.NotSubjectToOOPLimit then t.NotSubjectToOOPLimit end as Old,

case when t.NotSubjectToDeductible <> t1.NotSubjectToDeductible then t1.NotSubjectToDeductible when t.DeductibleNotes <> t1.DeductibleNotes then t1.DeductibleNotes when t.NotSubjectToOOPLimit <> t1.NotSubjectToOOPLimit then t1.NotSubjectToOOPLimit end as New

Well let's think about it. In the subquery, we say, "Give me all rows of t that are not in t1. We get one row back, which we join with the one row in t. In other words, the old and the new rows are the same. You can reverse the subsquery:

select * from @t1
except select * from @t

which says give me all the rows of t1 that are not also in t. We get one row back but this time its the row from t1. Makes more sense I think.

Note, however, that this is not set up to find all columns of t1 that are different...just the first one. To do all columns we're going to have to do more work. That is, if that's what you want.

The basic idea is to use set operations (EXCEPT, INTERSECT, UNION) to find rows that differ, then examine the rows column by column to find out what changed.

That is saying, "give me all the rows in @t1 that are not found in @t and also give me all the rows in @t that are not found in @t1."

It's important that you not only get a working solution, but that you understand why it works (or doesn't!). In this case I'm just using SQL Set operations which come directly from Relational Algebra upon which SQL is based.