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
Sql Server Merge 用法
最新推荐文章于 2024-08-25 10:01:19 发布