|
|
Posted by Erland Sommarskog on 10/15/05 00:51
(csomberg@dwr.com) writes:
> 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 ?
You can get the columns for a table with
SELECT name FROM syscolumns WHERE id = object_name('tbl')
I would suggest that it is best to generate the SQL statement from client
code, since client languages are better apt for string manipulation. You
can build SQL strings in T-SQL as well, and exeute them with EXEC() or
sp_executesql, but this is bulkier.
If you want to be static, one option is to use a client-language to
generate a stored procedure.
Keep in mind that you cannot just say:
SELECT ...
FROM tbl a
JOIN audit_tbl b ON a.keycol = b.keycol
WHERE a.col1 <> b.col1
AND a.col2 <> b.col2
...
Correct is:
WHERE (a.col1 <> b.col1 OR
a.col1 IS NULL AND b.col1 IS NOT NULL OR
a.col1 IS NOT NULL AND b.col1 IS NULL)
AND
--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp
Navigation:
[Reply to this message]
|