MSSQL MERGE / IF NOT EXISTS 문 사용법

IF NOT EXISTS (SELECT * 

                 FROM hsscm_ocp.dbo.mpi_workorder 

WHERE PLANT = '@@plant'

      AND MATERIAL = '@@material'

      AND INPUT_DATE = '@@inputDate'

      AND OUTPUT_DATE = '@@outputDate')

  INSERT INTO hsscm_ocp.dbo.mpi_workorder (

PLANT

,MATERIAL

,INPUT_DATE

,OUTPUT_DATE

,QTY

,OPERATION

,RESOURCE

,ORDER_NO

,ORDER_DIV

,REMARK

,CREATE_ID

,CREATE_DATE

)VALUES(

'@@plant'

,'@@material'

,'@@inputDate'

,'@@outputDate'

, @@qty

,'@@operation'

,'@@resource'

,'@@orderNo'

,'@@orderDiv'

,'@@remark'

,'@@createId'

,getdate()

);

ELSE 

   UPDATE hsscm_ocp.dbo.mpi_workorder

      SET REMARK = '';

MERGE hsscm_ocp.dbo.mpi_workorder T1

      USING (VALUES(1)) AS Source (Number)

 ON  T1.PLANT = '1140'

      AND T1.MATERIAL = '10000003'

      AND T1.INPUT_DATE = '2016-12-23'

      AND T1.OUTPUT_DATE = '2016-12-23'

   WHEN MATCHED THEN 

     UPDATE 

SET  T1.REMARK = ''

   WHEN NOT MATCHED THEN

     INSERT   (

PLANT

,MATERIAL

,INPUT_DATE

,OUTPUT_DATE

,QTY

,OPERATION

,RESOURCE

,ORDER_NO

,ORDER_DIV

,REMARK

,CREATE_ID

,CREATE_DATE

)VALUES(

'1140'

,'10000003'

,'2016-12-23'

,'2016-12-23'

,'' 

,''

,'S311'

,'000010044747'

,'S'

,''

,'sysadmin'

,getdate()

);

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15

MERGE TB_USER AS T USING (     SELECT 'aaa' AS ID, 'Name#1' AS NAME     UNION ALL     SELECT 'bbb' AS ID, 'Name#2' AS NAME ) AS S ON (T.ID = S.ID) WHEN MATCHED THEN     UPDATE SET T.ID = S.ID,                T.NAME = S.NAME WHEN NOT MATCHED THEN     INSERT (ID, NAME)     VALUES (S.ID, S.NAME) OUTPUT $action, Inserted.*;

cs