sql中merge的用法

Merge语法编辑

MERGE [hint] INTO [schema ] table [t_alias]

USING [schema ]{ table | view | subquery } [t_alias]

ON ( condition )

WHEN MATCHED THEN merge_ update_clause

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