Compare Data column by column SQL

Posted on 2009-12-21
Last Modified: 2012-05-08
I have a table that is the history table. Basically it has following structure.
Instancecode, Corecode, value1, value2, value3....
1, ABC, 1,2,3....
2.ABC, 1,3,5....
3,ABC, 2,2,5...

I would like to do a function like exec telldiff(ABC, 3) By giving the corecode and the instancecode fo 3. It should be able to tell me. That Corecode ABC instance 3 is different from instance 3 on column Return value "Instancecode, Value1, Value2". I don't need to know what has been changed. Just need to know that said columne it has been changed.

Keep in mind, there are couple of hundred columns can get added anytime. So it would be best to see if there is a programmatically way to loop it thorugh. I tried to use the system_schemainfo. But that only give me column name. I don't know how to use that to get the column values.

Thank You
Question by:pengbsam

    Expert Comment

    i dont get ur question... please explain briefly.. what u want exactly..
    LVL 41

    Expert Comment

    If you need to know what has been changed then you have to tell how do you recongnize the change.

    You may either compare values from your table against some live data. Then you have to define what to compare and how.

    If you just need to know what has been changed in this history table then it should contain more records for each Instancecode, Corecode combination. By counting these records you may decide about the change (unchanged records have just one occurence):

    SELECT Instancecode, Corecode, COUNT(*)
       FROM HistoryTable
      GROUP BY Instancecode, Corecode
      HAVING COUNT(*) > 1

    If the HistoryTable contains just one record for each Instancecode, Corecode combination and values in these records are updated then you'll need to create UPDATE TRIGGER on HistoryTable and records update occurences in another (audit) table.
    LVL 31

    Expert Comment

    Are you trying to show the columns value1 and value2 that changed from instance2 to instance3 and that there could be up to a 100 such columns ?

    Author Comment

    Hello. Sorry my explaination wasn't very good... So let me try this again.

    Bases of the requirement is to know by instance which column changed from the previous instance. As exmaple in the original example. If function is give by corecode and by instance number. THen it should compare it against the same corecode by the record of previous instance. So in my example. if I asking for corecode ABC and Instace 3. Then it is compare below two records.
    2.ABC, 1,3,5....
    3,ABC, 2,2,5...

    Just comparing record fields for each record. Now this is not a big deal if we doing this static over defined columns. Worse case scenario I will just compare them manually.
    If (select value2 from history where corecode = ABC and instance = 3) !=
       (select value2 from history where corecode = ABC and instance = 2)

    But supposely if this is a return value of large amount of columns and maybe changed on demand. So to prevent me from change the query sets everytime there is a change. I was thinking using the system column list from the System_schemainfo.columns to cursor through all the oclumns of history table and do the same thing for each column like what I manually did above.

    So the question is
    1. Would that be the best way to do it? or are there better ways?
    2. How do I select columns name with variable .. LIke if I cursor through the listing from a select set. How then do I instead of do select value2..... Do select @columns (where @columns are the retrieve column name from the cursor)...

    I hope I didn't just make this worse. I do appreciate the help though. Thanks
    LVL 10

    Expert Comment

    Have a look at my examples.
    If you don't need to know which columns are different the first method with checksum function and clean and robust.
    If you need to know the columns, have a look at the query I have created.
    The problem is you cannot have it as a function and you would have to implement it as a stored procedure
    LVL 10

    Assisted Solution

    ok,here are the examples
    create table sampleA (id int, code varchar(10), value1 int, value2 varchar(10), value3 decimal(15,4))
    create table sampleB (id int, code varchar(10), value1 int, value2 varchar(10), value3 decimal(15,4))
    create function AreEqual ( @id as int) returns bit
    	return case when 
    			select *
    			from (
    				select *, checksum(*) chk 
    				from sampleA 
    				where id = @id 
    			) A
    			inner join 
    				select *, checksum(*) chk
    				from sampleB 
    				where id = @id 
    			) B
    			on = 
    			and A.chk = B.chk
    		then 1 else 0 end 
    insert into sampleA values (1,'abc',1,'test',15)
    insert into sampleB values (1,'abc',1,'test',15)
    insert into sampleA values (2,'xyz',1,'test',15)
    insert into sampleB values (2,'xyz',2,'test',15)
    select dbo.AreEqual(1)
    select dbo.AreEqual(2)
    	declare @columns varchar(4096)
    	set @columns = ''''''
    	select @columns = @columns + ' + case when A.' + column_name + '<>B.' + column_name + ' then ''' + column_name+'''+'', '' else '''' end'
    	from information_schema.columns 
    	where table_name = 'sampleA'
    	declare @sql varchar(4096)
    	set @sql = 'select, ' + @columns + ' from sampleA A inner join sampleB B on ='
    	exec (@sql)

    Open in new window

    LVL 51

    Accepted Solution

    Well, there are a couple of ways of achieving your results.

    We could just list the column names that have changed, but prefer to show what has changed... So two simple examples as a starting point. One is a single row of all the columns and a string within the column representing the change. The second is more tablualr listing each column as a row and the changes as columns.

    The sample table I have used is "my_history_table" so you would need to do a global change of that name (there are a few instance in amongst all that SQL)

    -- first example outputs a single row with a string representing the changes in the column that changed

    DECLARE @CoreCode varchar(50)
    DECLARE @InstanceCode int
    set @corecode = 'ABC'
    set @instancecode = 3

    DECLARE @PriorInstance int
    set @priorInstance = isnull((select max(instancecode) from my_history_table where corecode = @corecode and instancecode < @instancecode),0)

    DECLARE @sql varchar(max)
    SELECT @sql = isnull(@sql,'SELECT a.corecode,a.instancecode') + ', case when (a.['+column_name+ '] <> b.['+column_name+'] or b.['+column_name+'] is NULL) then ''now:''+convert(varchar,a.['+column_name+'])+'' was:'' + convert(varchar,b.['+column_name+']) else '''' end as ['+column_name+']'
    from information_schema.columns where table_name = 'my_history_table' and ordinal_position > 2
    set @sql = @sql + ' from my_history_table a left outer join my_history_table b on a.corecode = b.corecode and b.instancecode = '+convert(varchar,@priorinstance)+' where a.corecode = '''+@corecode+''' and a.instancecode = '+convert(varchar,@instancecode)
    Print @sql


    -- second example outputs a table listing the columns that have changed with the change

    DECLARE @CoreCode varchar(50)
    DECLARE @InstanceCode int
    set @corecode = 'ABC'
    set @instancecode = 3

    DECLARE @PriorInstance int
    set @priorInstance = isnull((select max(instancecode) from my_history_table where corecode = @corecode and instancecode < @instancecode),0)

    DECLARE @sql varchar(max)
    DECLARE @maxcol int
    SET @maxcol = isnull((select max(ordinal_position) from information_schema.columns where table_name = 'my_history_table'),0)   -- knowing how many columns...
    IF @maxcol > 0
    SELECT @sql = isnull(@sql,'') + 'SELECT a.corecode,' + convert(varchar,ordinal_position)+' as col_id, '''+ column_name + ''' as col_name,' + 'convert(varchar,a.['+column_name+ ']) as Current_Value, ' + 'convert(varchar,b.['+column_name+']) as Prior_Value
    from my_history_table a left outer join my_history_table b on a.corecode = b.corecode and b.instancecode = '+convert(varchar,@priorinstance)+'
    where  a.corecode = '''+@corecode+''' and a.instancecode = '+convert(varchar,@instancecode)+'
    and a.['+column_name+ '] <> b.['+column_name+']'+
    case when ordinal_position < @maxcol then '
    ' else '
    order by 1,2' end from information_schema.columns where table_name = 'my_history_table'
    --print @sql

    LVL 51

    Expert Comment

    by:Mark Wills
    what is wrong with my answer - or lof's ? both experts gave two methods

    Please see

    mark_wills : http:#a26125039
    lof : http:#a26108280


    Featured Post

    Maximize Your Threat Intelligence Reporting

    Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

    Join & Write a Comment

    Suggested Solutions

    This article explains all about SQL Server Piecemeal Restore with examples in step by step manner.
    Many companies are looking to get out of the datacenter business and to services like Microsoft Azure to provide Infrastructure as a Service (IaaS) solutions for legacy client server workloads, rather than continuing to make capital investments in h…
    Video by: Steve
    Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
    Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

    745 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    16 Experts available now in Live!

    Get 1:1 Help Now