Compare two tables using utilities

Database

Compare two tables using utilities

Dear Experts,

I am Newbi to Teradata. I used to compare the two tables as below.

Select * from SOURCE_TABLE
MINUS
Select * from TARGET_TABLE

The above procedure can only get the all the records from SOURCE_TABLE if it is not matches with TARGET_TABLE.

Is there any way to get column level mismatches. Please find below for more understanding.

SOURCE_TABLE
-----------------------------------
NAME|CITY|PINCODE
-----------------------------------
JUIL | OPUT | 8347648
KIMU| PETU | 0494744
OPEY| UEYU| 8912374

TARGET_TABLE

-----------------------------------
NAME|CITY|PINCODE
-----------------------------------
JUL | OPUT | 8347648
KIU| PETU | 049444
OPEY| UYU| 891237

Please find the mismatches report we i want to generate with the utilitiy

NAME|COLUMN_NAME|SOURCE_VALUE|TARGET_VALUE
------------------------------------------
JUIL  | PINCODE   |0494744     |049444
OPEY|  CITY           |UEYU        |UYU
OPEY| PINCODE   |8912374     |891237

Kindly let me know if we can do this in teradata. Your help is much appreciated.

Cheers. 

Tags (1)
2 REPLIES

Re: Compare two tables using utilities

Experts,

Could you please look in to the above post.? Your help is much appreciated ..

Junior Contributor

Re: Compare two tables using utilities

If you insist on a column level report you need to split all columns into rows before comparison like:

select *
from
(
select name, 'city' as column_name, city as source_value from source_table
union all
select name, 'pincode', pincode from source_table
) as s
full outer join
(
select name, 'city' as column_name, city as target_value from target_table
union all
select name, 'pincode', pincode from target_table
) as t
on s.name = t.name and s.column_name = t.columnname
where not ((s.source_value = t.target_value) or
(s.source_value is null and t.target_value is null))

Dieter