compare data between 2 tables which has more than 100 columns.

Database
Enthusiast

compare data between 2 tables which has more than 100 columns.

 

Hello,

 

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. 

4 REPLIES
Enthusiast

Re: compare data between 2 tables which has more than 100 columns.

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...

Teradata Employee

Re: compare data between 2 tables which has more than 100 columns.

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.

Enthusiast

Re: compare data between 2 tables which has more than 100 columns.

 

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 

 

Many thanks!

Tags (1)
Senior Apprentice

Re: compare data between 2 tables which has more than 100 columns.

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.