Is there anyway to compare the contents of two tables which has more than 100 columns.
Not with minus, full outer join as the column list is more.
Thanks in advance.
You exclude the 2 options that I would do... maybe hash the entire row or the entire table and then compare. But hashing the rows requires also the column definitions and hashing the table requires a UDF...
If one table has more columns than the other then you can only compare the columns they have in common, so why not use MINUS?
But I suspect you meant that each table has rows that are not in the other table. In that case, you could do two MINUS's. You could conceivably do a FULL OUTER JOIN (on all the columns?), and in the predicate select the result rows that have null values. But I'm beginning to think you might be hitting the limit on the size of an SQL statement here if you are selecting on each column. It seems to me it would be a lot easier to do two MINUS's.
Thanks for your suggestion.
Reason why i excluded MINUS and full outer join is table size is huge (90GB) and columns count is 100. Does hashing helps returning the result soon compared to minus and full outer join ? If so, could you please provide me a sample
Assuming that both table share the same PI columns a Full Outer Join is probably the best performer.
In case there are NULLable columns in the PI don't join on COALESCE, better use (table1.column = table2.column or table1.column is null and table2.column is null) to get a direct join without spooling.