April 4, 2007 at 9:25 am
Hello,
We have a reporting database that we have an issues with. We are trying to track down an issue with the extracts from another database. We are missing data in some cases and in other cases we have changes in prod that are not being replicated to the reporting system.
To try to track this down we want to run a full extract to another table and then compare the two. Some of the tables have 100+ columns. We need to output missing rows and rows that do not match on every column. What type of code would I need to accomplish something like that?
Thanks,
Brian
April 4, 2007 at 10:03 am
tedious code. Or pay for something like Data Compare from Red Gate. Might burn the $300 cost in time very, very quickly.
April 4, 2007 at 10:08 am
The first part is easy, to compare missing rows run this:
select
t1.*
from
table1 t1 left join table2 t2
on t1.col1 = t2.col1
where t2.col1 is null
For the second part, where you need to compare them by each column value, you have to write a cursor wich queries system columns table and builds dynamic query. Here is an example if it, you have to adapt it to your case.
declare
@col sysname,
@stmt
declare cur cursor
for
select
name
from sys.columns
where object_name(object_id)='table1'
order by column_id
set @stmt = 'select * from table1 t1 join table2 t2
on t1.col1 = t2.col1
where '
open cur
fetch cur into @col
while @@fetch_status<>(-1)
begin
set @stmt = @stmt +
' t1.' + @col + ' <> t2.' + @col + ' or ' + char(13) + char(10)
fetch cur into @col
end
close cur
deallocate cur
set @stmt = left(@stmt, len(@stmt) - 5) -- to remove last 'or'
print @stmt
execute (@stmt)
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply