Best way to compare contents of two tables in Teradata?

Question

When you need to compare two tables to see what the differences are, are there any tools or shortcuts you use, or do you handcode the SQL to compare the two tables?

Basically the core features of a product like Red Gate SQL Data Compare (schemas for my tables typically always match).

Background: In my SQL Server environment, I created a stored procedure which inspects the metadata of the two tables/views, creates a query (as dynamic sql) which joins the two tables on the specified key columns, and compares data in the compare columns, reporting key differences and data differences. The query can either be printed and modified/copied or just excecuted as is. We are not allowed to create stored procedures in our Teradata environment, unfortunately.

Answer 1

Sounds like a data profiling tool such as Talend's Open Profiler would make the most sense at that point.

You could write a BTEQ statement that builds the query similar to your SQL Server stored procedure and then export the dynamically built SQL. You can then in turn run that inside of your BTEQ. It might get cumbersome, but with enough determination you could probably mock something up.

Answer 2

I dont know if this is the right answer you are searching for.

sel * from database_name1.table_name1
minus
sel * from database_name2.table_name2;

you can do the same by selecting specific columns. This will basically give the non existent rows from table2 which are in table1.

If you were not looking for this type of answer, please ignore this and continue.

Also you can select like below.

select 
table1.keycol1,
table2.keycol2,
(table1.factcol1 - table2.factcol2) as diff
from table1
inner join 
table2
on table1.keycol1 = table2.keycol1
and table1.keycol2 = table2.keycol2
where diff <> 0

This was just an analysis which can give an idea. Please ignore any syntactical and programmatical errors. Hope this helps.