Easy Compare Differences in Tables

SELECT * FROM TABLE_A
MINUS
SELECT * FROM TABLE_B

This can be expanded out to include the actual rows that are different between the two tables, or missing:




SELECT * FROM (

SELECT
'T906_TIME_CODE' AS TABLE_NAME
, '@AURPREP' AS DATABASE_NAME
, DECODE( -- Check if rows are different or missing. Missing if no row found with matching keys.
(SELECT 1 FROM T906_TIME_CODE B
WHERE NVL(B.T906F370_TRAN_CODE_TERM, 'X') = NVL(A.T906F370_TRAN_CODE_TERM, 'X')
AND NVL(B.T906F005_AWARD_CODE, 'X') = NVL(A.T906F005_AWARD_CODE, 'X')
AND NVL(B.T906F010_TIME_CODE, 'X') = NVL(A.T906F010_TIME_CODE, 'X')
AND NVL(B.T906F075_COSTING_ACCOUNT, 'X') = NVL(A.T906F075_COSTING_ACCOUNT, 'X')
AND NVL(B.T906F310_ACCOUNT, 'X') = NVL(A.T906F310_ACCOUNT, 'X')
AND NVL(B.T906F074_PC, 'X') = NVL(A.T906F074_PC, 'X')
AND NVL(B.T906F020_LEAVE_TYPE_AWARD, 'X') = NVL(A.T906F020_LEAVE_TYPE_AWARD, 'X')
AND NVL(B.T906F025_LEAVE_TYPE_CODE, 'X') = NVL(A.T906F025_LEAVE_TYPE_CODE, 'X')
AND NVL(B.T906F060_SECURITY_TASK, 'X') = NVL(A.T906F060_SECURITY_TASK, 'X')
) , 1, 'DIFFERENCES'
, 'MISSING IN CURRENT') AS STATUS
, A.*
FROM
(SELECT * FROM T906_TIME_CODE@AURPREP
MINUS
SELECT * FROM T906_TIME_CODE) A

UNION

SELECT
'T906_TIME_CODE' AS TABLE_NAME
, 'CURRENT' AS DATABASE_NAME
, DECODE( -- Check if rows are different or missing. Missing if no row found with matching keys.
(SELECT 1 FROM T906_TIME_CODE@AURPREP B
WHERE NVL(B.T906F370_TRAN_CODE_TERM, 'X') = NVL(A.T906F370_TRAN_CODE_TERM, 'X')
AND NVL(B.T906F005_AWARD_CODE, 'X') = NVL(A.T906F005_AWARD_CODE, 'X')
AND NVL(B.T906F010_TIME_CODE, 'X') = NVL(A.T906F010_TIME_CODE, 'X')
AND NVL(B.T906F075_COSTING_ACCOUNT, 'X') = NVL(A.T906F075_COSTING_ACCOUNT, 'X')
AND NVL(B.T906F310_ACCOUNT, 'X') = NVL(A.T906F310_ACCOUNT, 'X')
AND NVL(B.T906F074_PC, 'X') = NVL(A.T906F074_PC, 'X')
AND NVL(B.T906F020_LEAVE_TYPE_AWARD, 'X') = NVL(A.T906F020_LEAVE_TYPE_AWARD, 'X')
AND NVL(B.T906F025_LEAVE_TYPE_CODE, 'X') = NVL(A.T906F025_LEAVE_TYPE_CODE, 'X')
AND NVL(B.T906F060_SECURITY_TASK, 'X') = NVL(A.T906F060_SECURITY_TASK, 'X')
) , 1, 'DIFFERENCES'
, 'MISSING IN @AURPREP') AS STATUS
, A.*
FROM
(SELECT * FROM T906_TIME_CODE
MINUS
SELECT * FROM T906_TIME_CODE@AURPREP) A

)
ORDER BY -- (keys)
T906F370_TRAN_CODE_TERM
, T906F005_AWARD_CODE
, T906F010_TIME_CODE
, T906F075_COSTING_ACCOUNT
, T906F310_ACCOUNT
, T906F074_PC
, T906F020_LEAVE_TYPE_AWARD
, T906F025_LEAVE_TYPE_CODE
, T906F060_SECURITY_TASK
, 2
;

0 comments: