컬럼 비교쿼리 샘플(소스비교 같이)

 

TFS같이 소스 비교할 때 사용할 수 있는 쿼리문

 

 

WITH KEY_TBL
AS (
        SELECT DISTINCT tblKey FROM TESTAA
)
, S_TBL
AS (
    SELECT
        a.tblKey AS oriKey
    ,   b.tblKey
    ,   b.tblVersion
    ,   b.tblText
    FROM KEY_TBL a
    LEFT OUTER JOIN TESTAA b  ON  a.tblKey = b.tblKey AND b.tblVersion = 1
)
, T_TBL
AS (
    SELECT
        a.tblKey AS oriKey
    ,   b.tblKey
    ,   b.tblVersion
    ,   b.tblText
    FROM KEY_TBL a
    LEFT OUTER JOIN TESTAA b  ON  a.tblKey = b.tblKey AND b.tblVersion = 2
)
SELECT
        a.oriKey
    ,   a.tblKey
    ,   a.tblVersion
    ,   a.tblText
    ,   b.oriKey
    ,   b.tblKey
    ,   b.tblVersion
    ,   b.tblText
    ,   CASE WHEN  a.tblKey IS NULL THEN 'I' ELSE
            CASE WHEN b.tblKey IS NULL THEN 'D' ELSE
                CASE WHEN a.tblText <> b.tblText  THEN  'C' ELSE 'N' END
            END
        END
FROM S_TBL a
LEFT OUTER JOIN T_TBL b ON a.oriKey = b.oriKey
;