I want to compare two tables which have exactly same columns. Tables contains 200 columns each and have million of records in them.
My query is I want to compare these two tables and find out which columns have different data and then I need to populate those column names along with values that are mismatching into one another table for reporting purpose.
eg:
Table 1
Col1 col2 col3 col4
1 T R J
2 G K L
3 H O P
Table 2
Col1 col2 col3 col4
1 T Y J
2 R K L
3 H B A
Expected Result Table:
col_a col_b col_c col_d col_e
1 col3 R col3 Y
2 col2 G col2 R
3 col3 O col3 B
3 col4 P col4 A
Sounds like what you want to do is:
- Join on col1
- Unpivot the results so you have a row/column pair
- Return rows where t1cx <> t2cx
drop table t1 purge;
drop table t2 purge;
CREATE TABLE t1 (Col1 int, col2 varchar(1), col3 varchar(1), col4 varchar(1));
CREATE TABLE t2 (Col1 int, col2 varchar(1), col3 varchar(1), col4 varchar(1));
INSERT INTO t1 VALUES (1, 'T', 'R', 'J');
INSERT INTO t1 VALUES (2, 'G', 'K', 'L');
INSERT INTO t1 VALUES (3, 'H', 'O', 'P');
INSERT INTO t2 VALUES (1, 'T', 'Y', 'J');
INSERT INTO t2 VALUES (2, 'R', 'K', 'L');
INSERT INTO t2 VALUES (3, 'H', 'B', 'A');
select * from (
select t1.col1, t1.col2 t1c2, t1.col3 t1c3, t1.col4 t1c4,
t2.col2 t2c2, t2.col3 t2c3, t2.col4 t2c4
from t1 join t2
on t1.col1 = t2.col1
)
unpivot (
(t1, t2) for c in ((t1c2, t2c2), (t1c3, t2c3), (t1c4, t2c4))
)
where t1 <> t2;
COL1 C T1 T2
1 T1C3_T2C3 R Y
2 T1C2_T2C2 G R
3 T1C3_T2C3 O B
3 T1C4_T2C4 P A
If you want to know more about unpivoting, read:
https://blogs.oracle.com/sql/entry/how_to_convert_rows_to#unpivot