Sql Server Merge 用法

merge into [DN_DEPARTMENT] as d

using [sys_projectInfo] as p

on d.MYGUID = p.LandId

when MATCHED

THEN UPDATE SET d.DEPARTMENT_TYPE_NAME= '項目公司',

d.DEPARTMENT_NAME=p.LandName,

d.DEP_LEVEL=5,

d.DEP_IS_LEVEL='是',

d.LAST_CHANGED = GETDATE(),

d.STATUS = p.LandStatus,

d.DEPT_IS_ABANDON=0,

d.PARENT_ID = (select top 1 DEPARTMENT_ID from [DN_DEPARTMENT] where [COLNUM4] = p.CorpId),

d.DEPT_PARENTNAME = p.CorpName,

d.DEPT_IS_AVAILABLE = 1,

d.COLNUM4 = p.LandId,

d.DEPT_FORM = 2,

d.WORKFLOW_ORG =  (select top 1 WORKFLOW_ORG+'/'+p.LandName from [DN_DEPARTMENT] where [COLNUM4] = p.CorpId ),

d.MYGUID = p.LandId,

d.MYProjName = p.LandName

when NOT MATCHED

THEN INSERT VALUES(null

      ,null

      ,'項目公司'

      ,p.LandName

      ,null

      ,5

      ,'是'

      ,GETDATE()

      ,GETDATE()

      ,p.LandStatus

      ,9999999

      ,0

      ,(select top 1 DEPARTMENT_ID from [DN_DEPARTMENT] where [COLNUM4] = p.CorpId)

      ,p.CorpName

      ,null

      ,1

      ,0

      ,null

      ,null

      ,null

      ,null

      ,null

      ,p.LandId

      ,2

      ,(select top 1 WORKFLOW_ORG+'/'+p.LandName from [DN_DEPARTMENT] where [COLNUM4] = p.CorpId )

      ,p.LandId

      ,p.LandName ) 

WHEN NOT MATCHED BY SOURCE  

THEN DELETE

    OUTPUT $ACTION AS [ACTION],

   Deleted.ID AS 'Deleted ID',

   Deleted.DSPT AS 'Deleted Description',

   Inserted.ID AS 'Inserted ID',

   Inserted.DSPT AS 'Inserted Description'

END