SQL If there is an update, add if it does not exist, use the Merge function (SQL2008 version and above)

 Use [nationalunion]GO/** * * * object:storedprocedure [dbo].    [Proc_datasummary] Script date:07/03/2014 15:33:11 * * * * **/SETAnsi_nulls onGOSETQuoted_identifier onGOAlter proc [dbo].[proc_datasummary20140703](@UserID bigint--,--@Level int) asbegin    beginTrybegin Tran        Declare @Level int        Set @Level=1 --The default level is 1 for the last share        ----------------------------------------PV----------------------------------------------------------------------------- ----------------------------------------------------            --PVMerge intoDbo. Datasummary asDS using (SelectShareduserid,platformid,CONVERT(varchar( -), CreateDate,111) asCreatdate,channelid,sharedmanagerid,COUNT(*) asQty fromDbo. PvinfowhereSharedlevel= @Level  and(@UserID<0 orShareduserid= @UserID)Group  byShareduserid,platformid,channelid,sharedmanagerid,CONVERT(varchar( -), CreateDate,111)) asP on(DS. Userid=P.shareduserid andDs. PlatformID=P.platformid andDs. Channelid=P.channelid andDs. ManagerID=P.sharedmanagerid andDs. Summarydate=p.creatdate) whenMatched Then                Update SetPV=(PV+P.qty)--UPDATE STATISTICS already existing information             when  notMatched Then--Statistics of new PV information                Insert Values(P.shareduserid,p.platformid,p.creatdate,p.channelid,p.sharedmanagerid,p.qty,0,0,0,0,0,0,GETDATE()); ----------------------------------------UV----------------------------------------------------------------------------- ----------------------------------------------------            --UVMerge intoDbo. Datasummary asDS using (SelectShareduserid,platformid,CONVERT(varchar( -), CreateDate,111) asCreatdate,channelid,sharedmanagerid,COUNT(*) asQty fromDbo. UvinfowhereSharedlevel= @Level  and(@UserID<0 orShareduserid= @UserID)Group  byShareduserid,platformid,channelid,sharedmanagerid,CONVERT(varchar( -), CreateDate,111)) asU on(DS. Userid=U.shareduserid andDs. PlatformID=U.platformid andDs. Channelid=U.channelid andDs. ManagerID=U.sharedmanagerid andDs. Summarydate=u.creatdate) whenMatched Then                Update SetUv=(UV+U.qty)--UPDATE STATISTICS already existing information             when  notMatched Then--Statistics of new PV information                Insert Values(U.shareduserid,u.platformid,u.creatdate,u.channelid,u.sharedmanagerid,0, U.qty,0,0,0,0,0,GETDATE()); ----------------------------------------is expected to be a gold------------------------------------------------------------------------- --------------------------------------------------------Merge intoDbo. Datasummary asDS using (SelectShareduserid,platformid,CONVERT(varchar( -), Createtime,111) asCreatdate,channelid,sharedmanagerid,sum(Commission) asTotalestimatecommission fromDbo. CpsoridataoccurGroup  byShareduserid,platformid,channelid,sharedmanagerid,CONVERT(varchar( -), Createtime,111)) asO on(DS. Userid=O.shareduserid andDs. PlatformID=O.platformid andDs. Summarydate=O.creatdate andDs. Channelid=O.channelid andDs. ManagerID=O.sharedmanagerid) whenMatched Then--expected to be a gold                Update SetDs.commissionestimate=(ds.commissionestimate+o.totalestimatecommission) when   notMatched Then                Insert Values(O.shareduserid,o.platformid,o.creatdate,o.channelid,o.sharedmanagerid,0,0,0,0, O.totalestimatecommission,0,0,GETDATE()); ----------------------------------------can settle the gold-------------------------------------------------------------------------- -------------------------------------------------------Merge intoDbo. Datasummary asDS using (SelectShareduserid,platformid,CONVERT(varchar( -), Createtime,111) asCreatdate,channelid,sharedmanagerid,SUM(Price) asTotalprice,sum(Commission) asTotalcommission fromDbo. CpsoridataeffectGroup  byShareduserid,platformid,channelid,sharedmanagerid,CONVERT(varchar( -), Createtime,111)) asE on(DS. Userid=E.shareduserid andDs. PlatformID=E.platformid andDs. Summarydate=E.creatdate andDs. Channelid=E.channelid andDs. ManagerID=E.sharedmanagerid) whenMatched Then                Update SetDs. OrderAmount=(ds. OrderAmount+E.totalprice), DS. Avaliablecommission=(ds.commissionestimate+e.totalcommission) when   notMatched Then                Insert Values(E.shareduserid,e.platformid,e.creatdate,e.channelid,e.sharedmanagerid,0,0,0, E.totalprice,0, E.totalcommission,0,GETDATE()); Commit Tran    EndTrybeginCatchrollback Tran    EndCatchEnd