Merge语法编辑
WHEN NOT MATCHED THEN merge_insert_clause;
----------------------以下为存储过程中应用的示例--------------------------------------------------------------------------------------------------------------------------------------------------------------------------
USE [myRIS612]
GO
/****** Object: StoredProcedure [dbo].[Proc_TakeReportReleaseContent] Script Date: 09/02/2015 15:09:40 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create proc [dbo].[Proc_TakeReportReleaseContent] @OpenDebugLog BIT ='FALSE' --
as
DECLARE @LeastStamp ROWVERSION --时间戳--
SELECT @LeastStamp=Stamp FROM p_DataVersion WHERE DMLType=4
declare @ShortReportID int --ShortReportID 暂未用到
DECLARE @XSLID nvarchar(24)--5
declare @LastActiveDateTime datetime
declare @LastActiveUserCode nvarchar(64)
set @LastActiveDateTime=GETDATE()--赋值
set @LastActiveUserCode=@@SERVERNAME
--set @XSLID=''
set @ShortReportID=0
BEGIN TRY
BEGIN TRANSACTION DML;
MERGE dbo.ReportReleaseContent AS Target
USING (SELECT distinct RP.ReportID,RP.ImageFinding, RP.Impression,
'<?xml version="1.0" encoding="gb2312" ?> <?xml-stylesheet type="text/xsl" href="cdcatalog1.xsl"?>'+(select top 1 --xml生成的自段
RPC.ReportID,RPC.ImageFinding, RPC.Impression,RPC.ReportPhysicianCode,RPC.ReportDateTime,
RPC.VerifyDoctorCode1, RPC.VerifyDoctorCode2, RPC.VerifyDateTime1, RPC.VerifyDateTime2,RPC.CurrentStatus,
RPC.Positive,RPC.Status,
PAT.PatName, PAT.PatOtherName,datediff(YY,PAT.Birthday,od.OrderDateTime)AS PatAge,
dbo.GetDictionary('Sex',pat.SexCode,'DisplayText') as Sex,
dbo.GetDictionary('DepartmentInfo',od.DepartmentCode,'DisplayText') as DepartmentName,
pids.PatNumber, dbo.GetDictionary('Ward',vst.WardCode,'DisplayText') as OutpatientName,
dbo.GetDictionary('Staff',RPC.ReportPhysicianCode,'DisplayText') as ReportPhysicianName,
PAT.IdentityNumber,
pids.PatNumber,od.DepartmentCode,vst.OutpatientCode,vst.WardCode,vst.BedNumber,vst.InpatientCode,EXM.RegistrationDateTime,
RPC.EquipeType,RPC.BiopsySiteCode,RPC.Proposal,RPC.HP,
RPC.ReportBarcode, RPC.ReportPhysicianCode
from ReportContent as RPC
INNER JOIN Examination AS EXM ON RPC.ReportID=EXM.ReportID
INNER JOIN [Order] AS OD ON EXM.OrderID=OD.OrderID
INNER JOIN Visit AS VST ON OD.VisitID=VST.VisitID
INNER JOIN (SELECT PatID,PatNumber FROM PatientIdentify WHERE NTypeCode='0003') AS PIDS ON VST.PatID=PIDS.PatID
INNER JOIN Patient AS PAT ON VST.PatID=PAT.PatID
WHERE 1=1
And (@LeastStamp IS NULL OR RP.Stamp>@LeastStamp)
AND CurrentStatus>=140
and RP.ReportID=rpc.ReportID
FOR XML RAW('patientInfo'),ELEMENTS XSINIL,ROOT('SIMED_REPORT'))as XMLContent,------xml字段结束
rp.ReportPhysicianCode,RP.ReportDateTime,
RP.VerifyDoctorCode1, RP.VerifyDoctorCode2, RP.VerifyDateTime1, RP.VerifyDateTime2,RP.CurrentStatus,
RP.Positive,RP.Status
from ReportContent as RP
INNER JOIN Examination AS EXM ON RP.ReportID=EXM.ReportID
INNER JOIN [Order] AS OD ON EXM.OrderID=OD.OrderID
INNER JOIN Visit AS VST ON OD.VisitID=VST.VisitID
INNER JOIN (SELECT PatID,PatNumber FROM PatientIdentify WHERE NTypeCode='0003') AS PIDS ON VST.PatID=PIDS.PatID
INNER JOIN Patient AS PAT ON VST.PatID=PAT.PatID
WHERE 1=1
and(@LeastStamp IS NULL OR RP.Stamp>@LeastStamp)
AND CurrentStatus>=140)
AS Source(ReportID,ImageFinding,Impression,XMLContent,ReportPhysicianCode,ReportDateTime,VerifyDoctorCode1,
VerifyDoctorCode2,VerifyDateTime1,VerifyDateTime2,CurrentStatus,Positive,Status)
ON (Target.ReportID=Source.ReportID)
WHEN MATCHED THEN
UPDATE SET Target.ImageFinding=Source.ImageFinding
,Target.Impression=Source.Impression
,Target.XMLContent=Source.XMLContent
,Target.ReportPhysicianCode=Source.ReportPhysicianCode
,Target.ReportDateTime=Source.ReportDateTime
,Target.VerifyDoctorCode1=Source.VerifyDoctorCode1
,Target.VerifyDoctorCode2=Source.VerifyDoctorCode2
,Target.VerifyDateTime1=Source.VerifyDateTime1
,Target.VerifyDateTime2=Source.VerifyDateTime2
,Target.CurrentStatus=Source.CurrentStatus
,Target.Positive=Source.Positive
,Target.Status=Source.Status
--,Target.ShortReportID=Source.ReportID--ShortReportID
,Target.XSLID=''
--,Target.LastActiveDateTime=GETDATE()
--,Target.LastActiveUserCode=@@SERVERNAME
WHEN NOT MATCHED THEN
INSERT (ReportID,ShortReportID,ImageFinding,Impression,XMLContent,XSLID,ReportPhysicianCode,ReportDateTime,VerifyDoctorCode1,
VerifyDoctorCode2,VerifyDateTime1,VerifyDateTime2,CurrentStatus,Positive,LastActiveDateTime,LastActiveUserCode,Status)
VALUES(Source.ReportID,@ShortReportID,Source.ImageFinding,Source.Impression,Source.XMLContent,@XSLID,Source.ReportPhysicianCode,Source.ReportDateTime,Source.VerifyDoctorCode1,
Source.VerifyDoctorCode2,Source.VerifyDateTime1,Source.VerifyDateTime2,Source.CurrentStatus,Source.Positive,@LastActiveDateTime,@LastActiveUserCode,Source.Status);
--convert( int,Source.ReportID)
BEGIN
DELETE FROM dbo.[p_DataVersion]
WHERE [DMLType]=4
INSERT INTO dbo.[p_DataVersion]([DMLType],[Remark])
VALUES(4,N'黄浦区数据,ReportReleaseContent数据处理')
END
--记录日志
INSERT INTO [dbo].[p_AuditLogs]([UserId],[Username],[Location],[AuditTime],[ModuleName],[EventName],[TargetType],[TargetId],[FunctionResult],[LongDescription])
VALUES (@@SPID,'Proc_TakeReportReleaseContent',@@SERVERNAME,GETDATE(),'DataMove','DataMove','DataMove','ReportReleaseContent','Success','WKL Data Copy 2 LastYears.'+CAST(@@ROWCOUNT AS VARCHAR) +' row(s) affected.')
COMMIT TRANSACTION DML;
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION DML;
--记录日志
INSERT INTO [dbo].[p_AuditLogs]([UserId],[Username],[Location],[AuditTime],[ModuleName],[EventName],[TargetType],[TargetId],[FunctionResult],[LongDescription])
VALUES (@@SPID,'Proc_TakeReportReleaseContent',@@SERVERNAME,GETDATE(),'DataMove','DataMove','DataMove','ReportReleaseContent','Failed',ERROR_MESSAGE())
END CATCH
--------- exec Proc_TakeReportReleaseContent
--select * from dbo.ReportReleaseContent
-- select * from p_DataVersion
-- select * from p_AuditLogs order by audittime desc
-------删除delete from p_DataVersion where DMLType=4