利用游标实现按会计月汇总数据，利用MERGE INTO实现数据库添加或更新

CREATE  PROCEDURE [dbo].[sys_btainC6]
AS
    Create Table #c6bx
    (

        bmid  varchar(100),    
        bm  varchar(100),    
        je  decimal(28,2),
        ssgs  varchar(300),
        kjnd  varchar(100),
        kjqj  varchar(100)
    )
    BEGIN
    DECLARE @bmid varchar(50) 
DECLARE @gs varchar(50) 
DECLARE @kjnd varchar(50) 
DECLARE @kjqj int 
DECLARE kjya CURSOR --定义游标
FOR (SELECT DISTINCT bmid,(jbcsj.dbo.fn_getgs (bmid)) gs,YEAR(bxrq) as kjnd, MONTH(bxrq)as kjqj FROM [c6].[dbo].[cw_fybxcb]a INNER JOIN c6.dbo.cw_fybx b on a.MainID=b.mainid left join  c6.dbo.ModuleApproveFlag c on a.mainid=c.id where 1=1 and DelFlag='0' and app_flag='1' and  fylx='费用类') --查出需要的集合放到游标中
OPEN kjya --打开游标
FETCH NEXT FROM kjya INTO @bmid,@gs,@kjnd,@kjqj --读取第一行数据
WHILE @@FETCH_STATUS = 0
    BEGIN
INSERT INTO #c6bx
       SELECT DISTINCT bmid,bm,sum(je) as je,(jbcsj.dbo.fn_getgs (bmid)) as ssgs,@kjnd as kjnd, @kjqj as kjqj FROM [c6].[dbo].[cw_fybxcb]a INNER JOIN c6.dbo.cw_fybx b on a.MainID=b.mainid
left join  c6.dbo.ModuleApproveFlag c on a.mainid=c.id where 1=1 and DelFlag='0' and app_flag='1' and  fylx='费用类'
 and bxrq BETWEEN cast((@kjnd+'-'+cast((@kjqj-1) as VARCHAR(2))+ '-26') as date)  and  cast((@kjnd+'-'+cast(@kjqj as VARCHAR(2))+ '-25')as date) 
and bmid=@bmid
 and @gs is not null GROUP BY bm,bmid
        FETCH NEXT from kjya INTO @bmid,@gs,@kjnd,@kjqj--读取下一行数据
    END
CLOSE kjya; --关闭游标
DEALLOCATE kjya; --释放游标
MERGE INTO [jbcsj].[dbo].[bmb]
USING (SELECT bmid,bm,je,ssgs,kjnd,kjqj FROM #c6bx ) cc
ON (bmb.bmid=cc.bmid and bmb.ssgs=cc.ssgs and bmb.kjnd=cc.kjnd and bmb.kjqj=cc.kjqj) 
WHEN MATCHED THEN 
    UPDATE 
    SET bmb.je=cc.je
WHEN NOT MATCHED THEN 
    INSERT([bm], [je], [ssgs], [kjnd], [kjqj], [bmid]) VALUES (cc.bm,cc.je,cc.ssgs,cc.kjnd,cc.kjqj,cc.bmid);

    drop table #c6bx
END

 

MERGE INTO 目标表 USING 数据源表 ON(0=1) 的用法

转载于:https://www.cnblogs.com/lydg/p/11368748.html