利用Merge生成或更新新记录

-- =============================================
-- Author:        <华仔>
-- Create date: <2016,6,7>
-- Description:    <鱼种统计>
-- Update_QT_FishTypeCount 0,0,0,0 -- 测试可否正常运行
-- 维护日志：
--
-- =============================================
ALTER PROCEDURE [dbo].[Update_QT_FishTypeCount]
@RoomLevel INT,
@FishType INT,
@AppearNumber INT,
@CatchNumber INT

AS
BEGIN
    
    SET NOCOUNT ON;

    BEGIN TRY--错误捕获
    BEGIN TRAN; -- 开始事务

    WITH N AS(
        SELECT @RoomLevel AS RoomLevel
            ,CONVERT(CHAR(10),GETDATE(),23) AS CountDate
            ,@FishType AS FishType
            ,@AppearNumber AS AppearNumber
            ,@CatchNumber AS CatchNumber
    )
    MERGE QT_FishTypeCount AS FTC
    USING N ON N.CountDate = FTC.CountDate AND N.RoomLevel = FTC.RoomLevel AND N.FishType = FTC.FishType
    WHEN NOT MATCHED 
    THEN INSERT
        (
            [RoomLevel],
            [CountDate],
            [FishType],
            [AppearNumber],
            [CatchNumber]
        )
        VALUES
        (
            N.[RoomLevel],
            N.[CountDate],
            N.[FishType],
            N.[AppearNumber],
            N.[CatchNumber]
        )
    WHEN MATCHED 
    THEN UPDATE SET [AppearNumber] = FTC.[AppearNumber] + @AppearNumber,[CatchNumber] = FTC.[CatchNumber] + @CatchNumber;

    COMMIT TRAN -- 执行无错误，提交事务
    END TRY--错误捕获
    BEGIN CATCH--错误捕获
        ROLLBACK TRAN -- 执行出错，回滚事务
        INSERT INTO [QPAccountsDB].[dbo].[QA_ErrorLog]
            ([ErrorNumber]
            ,[ErrorSeverity]
            ,[ErrorState]
            ,[ErrorProcedure]
            ,[ErrorLine]
            ,[ErrorMessage]
            ,[ErrorTime])
        SELECT ERROR_NUMBER()
            ,ERROR_SEVERITY()
            ,ERROR_STATE()
            ,'QPTreasureDB.dbo.Update_QT_FishTypeCount'
            ,ERROR_LINE()
            ,ERROR_MESSAGE()
            ,GETDATE()
    END CATCH--错误捕获
END