MSSQL UPSERT 생성 :)

고민의 고민을 하다가.. MERGE를 사용하기로 결정.. UPSERT를 만들어보기로 함.

MERGE
[테이블명] AS T
        USING
            (SELECT [받는 값(필드명과 매치)]) AS S
            ([필드명])
        ON
            T.INDEX_SQ = S.INDEX_SQ
        WHEN MATCHED THEN

         [UPDATE 구문]
        WHEN  NOT MATCHED THEN
        [INSERT구문]

EX>

MERGE
            MST_ADMIN_TD AS T
        USING
            (SELECT #{index_sq}, #{name_nm},'B', 'Y', getdate(), #{user_id}, #{user_pwd}, #{group_sq}) AS S
            (INDEX_SQ, NAME_NM, ADMIN_GB, USE_FL, REG_DT, USER_ID, USER_PWD, GROUP_SQ)
        ON
            T.INDEX_SQ = S.INDEX_SQ
        WHEN MATCHED THEN
            UPDATE    SET
                NAME_NM = S.NAME_NM,
                ADMIN_GB = S.ADMIN_GB,
                USE_FL = S.USE_FL,
                REG_DT = S.REG_DT,
                USER_ID = S.USER_ID,
                USER_PWD = S.USER_PWD,
                GROUP_SQ = S.GROUP_SQ
        WHEN  NOT MATCHED THEN
            INSERT
                (NAME_NM, ADMIN_GB, USE_FL, REG_DT, USER_ID, USER_PWD, GROUP_SQ)
            VALUES
                (S.NAME_NM, S.ADMIN_GB, S.USE_FL, S.REG_DT, S.USER_ID, S.USER_PWD, S.GROUP_SQ)

OUTPUT $action, INSERTED.*;

제일 중요한 키포인트는 OUTPUT!!!! 해당되는 값을 RETURN 받기 위해서는 OUTPUT을 기재해야한다.
OUTPUT $action, INSERTED.*; >> INSERT일때
OUTPUT $action, DELETED.*; >> DELETE일때
OUTPUT $action, DELETED.*,INSERTED.*; >> UPDATE일때
사용하면 된다! :)