Comparing all fields of two rows in few tables.

The problem scenario is this:
I have an application which uses few tables.
user can post data and can submit the form multiple times.
each posting is saved as each version.
Now that I have all data, I need to find the difference between 2
versions of the saved data
and report that he has modified these fields in the current submission
to the older.
what is the best way to compare all the fields(except for Primary key)
of 2 rows in a table.
I am thinking of doing this -
select
case when oldversion.Field1 <> newversion.Field1 then 'changed' as
Field1 end,
case when oldversion.Field2 <> newversion.Field2 then 'changed' as
Field2 end
from
(select * from Table1 where TablePKField = oldPKID ) oldversion
inner join
(select * from Table1 where TablePKField = NewPKID ) newversion
where newversion.commonField = newversion.CommonField
Please suggest me the best way of doing this without much performance
loss...
Thanks for your time.
G.Gees
if (select checksum_agg(checksum(*)) from t1)
<> (select checksum_agg(checksum(*)) from t2)
print 'different'
else
print 'probably the same'
"Gees" <gayathri.s@.gmail.com> wrote in message
news:1141032918.046365.85170@.j33g2000cwa.googlegroups.com...
> The problem scenario is this:
> I have an application which uses few tables.
> user can post data and can submit the form multiple times.
> each posting is saved as each version.
> Now that I have all data, I need to find the difference between 2
> versions of the saved data
> and report that he has modified these fields in the current submission
> to the older.
> what is the best way to compare all the fields(except for Primary key)
> of 2 rows in a table.
> I am thinking of doing this -
> select
> case when oldversion.Field1 <> newversion.Field1 then 'changed' as
> Field1 end,
> case when oldversion.Field2 <> newversion.Field2 then 'changed' as
> Field2 end
> from
> (select * from Table1 where TablePKField = oldPKID ) oldversion
> inner join
> (select * from Table1 where TablePKField = NewPKID ) newversion
> where newversion.commonField = newversion.CommonField
> Please suggest me the best way of doing this without much performance
> loss...
> Thanks for your time.
> G.
>|||Thanks Uri Dimant.
My problem also includes, quering those fields where the data is
changed and show only the changes.
something like In Table1 ,
Field1- Field 2 - Field3
Row1 A - B - C
Row2 A - X - Y
I need to show that,
>From Row1 to Row2
Values of Field2 , B to X
and Values of Field3 C to Y
are the changes.
any help ?
Thanks again!
G
Uri Dimant wrote:
> Gees
> if (select checksum_agg(checksum(*)) from t1)
> <> (select checksum_agg(checksum(*)) from t2)
> print 'different'
> else
> print 'probably the same'
>
>
> "Gees" <gayathri.s@.gmail.com> wrote in message
> news:1141032918.046365.85170@.j33g2000cwa.googlegroups.com...|||Gees
CREATE TABLE [dbo].Audit (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[Col1] [varchar] (50) NOT NULL ,
[Col2] [int] NOT NULL ,
[Col3] [varchar] (255) NOT NULL ,
[Col4] [int] NOT NULL
) ON [PRIMARY]
And it has the following records:
INSERT INTO Audit VALUES ('Andy', 3, 'Oxford', 21)
INSERT INTO Audit VALUES ('Andy', 4, 'Oxford', 21)
INSERT INTO Audit VALUES ('Andy', 4, 'Cambridge', 21)
INSERT INTO Audit VALUES ('Andy', 6, 'Cambridge', 29)
INSERT INTO Audit VALUES ('Andy', 4, 'Manchester', 21)
ID ChangedColumn NewVal
2 Col2 4
3 Col3 Cambridge
4 Col2 6
4 Col4 29
5 Col3 Manchester
select a2.id,'col2' as colchng, cast(a2.col2 as varchar(255)) as newvalue
from audit a1 join audit a2 on a1.id=a2.id-1
where a1.col2<>a2.col2
union all
select a2.id,'col3', a2.col3
from audit a1 join audit a2 on a1.id=a2.id-1
where a1.col3<>a2.col3
union all
select a2.id,'col4', cast(a2.col4 as varchar(255))
from audit a1 join audit a2 on a1.id=a2.id-1
where a1.col4<>a2.col4
order by 1,2
"Gees" <gayathri.s@.gmail.com> wrote in message
news:1141042195.525248.161020@.i39g2000cwa.googlegroups.com...
> Thanks Uri Dimant.
> My problem also includes, quering those fields where the data is
> changed and show only the changes.
> something like In Table1 ,
> Field1- Field 2 - Field3
> Row1 A - B - C
> Row2 A - X - Y
> I need to show that,
> Values of Field2 , B to X
> and Values of Field3 C to Y
> are the changes.
> any help ?
> Thanks again!
> G
> Uri Dimant wrote:
>
>|||Hi Uri Dimant,
Thanks for the quick and nice reply.
Thats very useful.
Thanks a ton!
Best,
G