pengbsam
asked on
Compare Data column by column SQL
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
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
i dont get ur question... please explain briefly.. what u want exactly..
If you need to know what has been changed then you have to tell how do you recongnize the change.
1)
You may either compare values from your table against some live data. Then you have to define what to compare and how.
2)
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
3)
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.
1)
You may either compare values from your table against some live data. Then you have to define what to compare and how.
2)
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
3)
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.
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 ?
ASKER
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)
BEGIN
RETURNBALUE = RETUREBVALUE + ' VALUE 2'
END
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
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)
BEGIN
RETURNBALUE = RETUREBVALUE + ' VALUE 2'
END
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
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
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
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
what is wrong with my answer - or lof's ? both experts gave two methods
Please see
mark_wills : http:#a26125039
lof : http:#a26108280
thanks...
Please see
mark_wills : http:#a26125039
lof : http:#a26108280
thanks...