SQL 实现，如果存在就更新，如果不存在就添加

alter proc proc_DataSummary
as
begin
    begin try
        begin tran
            --PV
            --统计的信息存入临时表
            select SharedUserID,PlatformID,CONVERT(varchar(100), CreateDate, 111) as CreatDate,ChannelID,SharedManagerID,COUNT(*) as Qty into #PVData from dbo.PVInfo where SharedLevel=1 group by SharedUserID,PlatformID,ChannelID,SharedManagerID,CONVERT(varchar(100), CreateDate, 111)
            --更新将统计己存在的信息
            update dbo.DataSummary set PV=dbo.#PVData.Qty from #PVData
            where dbo.DataSummary.UserID=dbo.#PVData.SharedUserID and dbo.DataSummary.PlatformID=dbo.#PVData.PlatformID and dbo.DataSummary.ChannelID=dbo.#PVData.ChannelID
            and dbo.DataSummary.ManagerID=dbo.#PVData.SharedManagerID and dbo.DataSummary.SummaryDate=dbo.#PVData.CreatDate
            --统计新的PV信息
            insert into dbo.DataSummary
            select SharedUserID,PlatformID,CreatDate,ChannelID,SharedManagerID,Qty,0,0,0,0,0,0,GETDATE() from dbo.#PVData a 
            where not exists(select * from dbo.DataSummary where dbo.DataSummary.UserID=a.SharedUserID and dbo.DataSummary.PlatformID=a.PlatformID and dbo.DataSummary.ChannelID=a.ChannelID 
            and dbo.DataSummary.ManagerID=a.SharedManagerID and dbo.DataSummary.SummaryDate=a.CreatDate )
        
            --UV
            --统计的信息存入临时表
            select SharedUserID,PlatformID,CONVERT(varchar(100), CreateDate, 111) as CreatDate,ChannelID,SharedManagerID,COUNT(*) as Qty into #UVData from dbo.UVInfo where SharedLevel=1 group by SharedUserID,PlatformID,ChannelID,SharedManagerID,CONVERT(varchar(100), CreateDate, 111)
            --更新将统计己存在的信息
            update dbo.DataSummary set UV=dbo.#UVData.Qty from #UVData
            where dbo.DataSummary.UserID=dbo.#UVData.SharedUserID and dbo.DataSummary.PlatformID=dbo.#UVData.PlatformID and dbo.DataSummary.ChannelID=dbo.#UVData.ChannelID
            and dbo.DataSummary.ManagerID=dbo.#UVData.SharedManagerID and dbo.DataSummary.SummaryDate=dbo.#UVData.CreatDate
            --统计新的UV信息
            insert into dbo.DataSummary
            select SharedUserID,PlatformID,CreatDate,ChannelID,SharedManagerID,Qty,0,0,0,0,0,0,GETDATE() from dbo.#UVData a 
            where not exists(select * from dbo.DataSummary where dbo.DataSummary.UserID=a.SharedUserID and dbo.DataSummary.PlatformID=a.PlatformID and dbo.DataSummary.ChannelID=a.ChannelID 
            and dbo.DataSummary.ManagerID=a.SharedManagerID and dbo.DataSummary.SummaryDate=a.CreatDate )
            
         commit tran
    end try
    begin catch
        rollback tran
    end catch
end

 

SQL 如果存在就更新，如果不存在就添加，使用 Merge 函数(SQL2008版本及以上)

转载于:https://www.cnblogs.com/xuxu-dragon/p/3816921.html