Compare Data column by column SQL

Posted on 2009-12-21
Medium Priority
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

ID: 26102353
i dont get ur question... please explain briefly.. what u want exactly..
LVL 43

Expert Comment

ID: 26104978
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

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 32

Expert Comment

ID: 26105643
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 ?
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!


Author Comment

ID: 26106588
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

ID: 26108272
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

lof earned 1000 total points
ID: 26108280
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 A.id = B.id 
			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 A.id, ' + @columns + ' from sampleA A inner join sampleB B on a.id = b.id'

	exec (@sql)

Open in new window

LVL 51

Accepted Solution

Mark Wills earned 1000 total points
ID: 26125039
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
ID: 26372708
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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this blog post, we’ll look at how using thread_statistics can cause high memory usage.
Recursive SQL is one of the most fascinating and powerful and yet dangerous feature offered in many modern databases today using a Common Table Expression (CTE) first introduced in the ANSI SQL 99 standard. The first implementations of CTE began ap…
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…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

850 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