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.
Author |
Topic |
byka
Starting Member
18 Posts |
Posted - 2014-08-18 : 12:55:35
|
How can I compare column in the same table and show only column and value that has been changed.declare @t table (GroupID CHAR(6),Text1 VARCHAR(MAX),Text2 VARCHAR(MAX),Text3 VARCHAR(MAX))insert into @t SELECT '11111','Text1','Text2','Text3'insert into @t SELECT '11111','Text1','Text2','Text4'END RESULTS:Column Name Old NewColumn 3 |'Text3' |'Text4'byka |
|
gbritton
Master Smack Fu Yak Hacker
2780 Posts |
Posted - 2014-08-18 : 13:48:23
|
here's one way:declare @t table (GroupID CHAR(6),Text1 VARCHAR(MAX),Text2 VARCHAR(MAX),Text3 VARCHAR(MAX))declare @t1 table (GroupID CHAR(6),Text1 VARCHAR(MAX),Text2 VARCHAR(MAX),Text3 VARCHAR(MAX))insert into @t SELECT '11111','Text1','Text2','Text3'insert into @t1SELECT '11111','Text1','Text2','Text4'select case when t.text1 <> t1.text1 then 1 when t.text2 <> t1.text2 then 2 when t.text2 <> t1.text3 then 3 end as [Column], case when t.text1 <> t1.text1 then t.text1 when t.text2 <> t1.text2 then t.text2 when t.text2 <> t1.text3 then t.text3 end as Old, case when t.text1 <> t1.text1 then t1.text1 when t.text2 <> t1.text2 then t1.text2 when t.text2 <> t1.text3 then t1.text3 end as Newfrom @t tjoin (select * from @texcept select * from @t1) t1on t.GroupID = t1.GroupID |
|
|
byka
Starting Member
18 Posts |
Posted - 2014-08-18 : 14:12:00
|
do you have more generic examples because I need to do this for different tables with different scheemabyka |
|
|
gbritton
Master Smack Fu Yak Hacker
2780 Posts |
Posted - 2014-08-18 : 14:15:48
|
YOu could build a similar query using dynamic SQL, but perhaps the right answer is Change Tracking. Here's a series of articles on the topic:http://solutioncenter.apexsql.com/methods-for-auditing-sql-server-data-changes-part-i-change-tracking/ |
|
|
byka
Starting Member
18 Posts |
Posted - 2014-08-18 : 14:24:29
|
I have adjusted and it stopped working...could you tell me why ?declare @t table (GroupID CHAR(6),NotSubjectToDeductible VARCHAR(MAX), DeductibleNotes VARCHAR(MAX), NotSubjectToOOPLimit VARCHAR(MAX))declare @t1 table (GroupID CHAR(6),NotSubjectToDeductible VARCHAR(MAX),DeductibleNotes VARCHAR(MAX),NotSubjectToOOPLimit VARCHAR(MAX))insert into @t (GroupID ,NotSubjectToDeductible , DeductibleNotes , NotSubjectToOOPLimit )SELECT '11111','NotSubjectToDeductible 4','DeductibleNotes','NotSubjectToOOPLimit 'insert into @t1 (GroupID ,NotSubjectToDeductible , DeductibleNotes , NotSubjectToOOPLimit )SELECT '11111','NotSubjectToDeductible ','DeductibleNotes','NotSubjectToOOPLimit 5'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 Newfrom @t tjoin ( select * from @texcept select * from @t1) t1on t.GroupID = t1.GroupIDbyka |
|
|
gbritton
Master Smack Fu Yak Hacker
2780 Posts |
Posted - 2014-08-18 : 15:04:12
|
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 @t1except 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. |
|
|
byka
Starting Member
18 Posts |
Posted - 2014-08-18 : 15:16:23
|
With Union it will list full row but I need to specify in what column and what value changed.byka |
|
|
gbritton
Master Smack Fu Yak Hacker
2780 Posts |
Posted - 2014-08-18 : 15:24:51
|
quote: Originally posted by byka With Union it will list full row but I need to specify in what column and what value changed.byka
Yes, of course. I wasn't suggesting you use Union, just listing the set operations. For your problem, EXCEPT is the right choice |
|
|
byka
Starting Member
18 Posts |
Posted - 2014-08-18 : 15:38:43
|
Here is what I come up with based on your advise... however not getting what I need...sorry I am new to sql so need some help ....My end result should look this this:Column Name OldValue newValueNotSubjectToDeductible NotSubjectToDeductible otSubjectToDeductible 4NotSubjectToOOPLimit NotSubjectToOOPLimit NotSubjectToOOPLimit 5declare @t table (GroupID CHAR(6),NotSubjectToDeductible VARCHAR(MAX), DeductibleNotes VARCHAR(MAX), NotSubjectToOOPLimit VARCHAR(MAX))declare @t1 table (GroupID CHAR(6),NotSubjectToDeductible VARCHAR(MAX),DeductibleNotes VARCHAR(MAX),NotSubjectToOOPLimit VARCHAR(MAX))insert into @t (GroupID ,NotSubjectToDeductible , DeductibleNotes , NotSubjectToOOPLimit )SELECT '11111','NotSubjectToDeductible 4','DeductibleNotes','NotSubjectToOOPLimit 'insert into @t1 (GroupID ,NotSubjectToDeductible , DeductibleNotes , NotSubjectToOOPLimit )SELECT '11111','NotSubjectToDeductible ','DeductibleNotes','NotSubjectToOOPLimit 5'SELECT * FROM @t EXCEPT SELECT * FROM @t1byka |
|
|
gbritton
Master Smack Fu Yak Hacker
2780 Posts |
Posted - 2014-08-19 : 08:31:43
|
OK -- I replaced the last part (the selects) with thi:SELECT * from ((select * FROM @t1 EXCEPT SELECT * FROM @t)UNION(select * FROM @t EXCEPT SELECT * FROM @t1)) s 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. |
|
|
|
|
|
|
|