Use merge to generate or update new records

1 -- =============================================2 --Author: < wah Tsai >3 --Create Date: <2016,6,7>4 --Description: < species statistics >5 --update_qt_fishtypecount 0,0,0,0--test can run properly6 --Maintenance log:7 --8 -- =============================================9 ALTER PROCEDURE [dbo].[Update_qt_fishtypecount]Ten @RoomLevel INT, One @FishType INT, A @AppearNumber INT, - @CatchNumber INT -  the  as - BEGIN -      -     SETNOCOUNT on; +  -     BEGINTRY--Error Trapping +     BEGIN TRAN;--Start a transaction A  at      withN as( -         SELECT @RoomLevel  asRoomlevel -,CONVERT(CHAR(Ten),GETDATE(), at) ascountdate -,@FishType  asFishtype -,@AppearNumber  asAppearnumber -,@CatchNumber  asCatchnumber in     ) -MERGE Qt_fishtypecount asFTC toUSING N onN.countdate=Ftc. Countdate andN.roomlevel=Ftc. Roomlevel andN.fishtype=FTC. Fishtype +      when  notmatched -      Then INSERT the         ( *             [Roomlevel], $             [countdate],Panax Notoginseng             [Fishtype], -             [Appearnumber], the             [Catchnumber] +         ) A         VALUES the         ( +N.[Roomlevel], -N.[countdate], $N.[Fishtype], $N.[Appearnumber], -N.[Catchnumber] -         ) the      whenmatched -      Then UPDATE SET [Appearnumber] =Ftc.[Appearnumber] + @AppearNumber,[Catchnumber] =Ftc.[Catchnumber] + @CatchNumber;Wuyi  the     COMMIT TRAN --Execute ERROR-free, COMMIT transaction -     ENDTRY--Error Trapping Wu     BEGINCatch--Error Trapping -         ROLLBACK TRAN --performing an error, rolling back a transaction About         INSERT  into [Qpaccountsdb].[dbo].[Qa_errorlog] $([errornumber] -,[errorseverity] -,[errorstate] -,[errorprocedure] A,[ErrorLine] +,[errormessage] the,[Errortime]) -         SELECTerror_number () $ , error_severity () the , Error_state () the,'QPTreasureDB.dbo.Update_QT_FishTypeCount' the , Error_line () the , Error_message () -,GETDATE() in     ENDCatch--Error Trapping the END

View Code

Use merge to generate or update new records