Column By Column Comparison

[csomberg]

SQL Server 2000

I will to compare a normal table with a replicated audit table having
identical columns. I wish to report on the differences between the
data.

How can I loop though a "column list" in TSQL rather than explicitly
naming each column to compare ?

Thanks,
Craig

[Alexander Kuznetsov]

if the table has a PK, there is no need to do tedios column-by-colmn
comparisons:

create table t1(i int identity, j int)
insert into t1(j) values(1)
insert into t1(j) values(2)
insert into t1(j) values(3)

create table t2(i int identity, j int)
insert into t2(j) values(1)
insert into t2(j) values(2)
insert into t2(j) values(4)

------ rows in t1 that do not have exact match in t2
select * from t1 t
---- there is a row in t2 with the same PK
where exists(select * from t2 where t2.i = t.i)
---- but some other columns are different
and
(select count(*) from
(
select * from t1
union
select * from t2
)t_both where t_both.i = t.i
) = 2
------ rows in t2 that do not have exact match in t1
select * from t2 t
---- there is a row in t1 with the same PK
where exists(select * from t1 where t1.i = t.i)
---- but some other columns are different
and
(select count(*) from
(
select * from t1
union
select * from t2
)t_both where t_both.i = t.i
) = 2

drop table t1
drop table t2

[csomberg]

Thank you all for the updates and I am sorry I did not get back sooner.

I need to do column bu column comparisons as the "report" has to show
which data elements have changed and from what to what ... yuk.

I do not think I can do this efficiently in SQL so I was thinking of
writing a quick little app to loop through column collections of the
recordset ...