Use merge to generate or update new records

Source: Internet
Author: User

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

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.