Using temporary tables to cycle data during [SQL Server] Storage

This article is original article, reprint please indicate the source! My blog address: http://www.cnblogs.com/txwd

As a result of the work, for more than a year now for this has not written SQL Server storage process, has been a little rusty. Recently work has a table of data needs to be updated regularly, turn over the previously written storage process, in this record.

The requirement is this:

There are two tables 1, bo Main table: blogger , 2, Bo master corresponding article table: Blogforblogger

The data of the article table is obtained by the service-side timing, the main table has a field to save the total number of bloggers, so this field should be updated regularly.

implementation: Create a storage process, and then execute the stored procedure in the database with a scheduled job.

-- =============================================--Author:li--Create date:2017-08-29--Description: Update the total number of Bo master articles-- =============================================CREATE PROCEDURESp_update_blogger_blog_articlecount asBEGIN        Declare @account varchar( -);--Bo Master Account    Declare @count int;--bo Master Number    Declare @i int;--Cyclic identification    Declare @articleCount int;--Number of articles    --Save all blogger information to a temporary table    Select *  into#Temp  from(SelectAccount,ranking,row_number () Over(Order  byRanking) asRow fromBlogger) bSelect @count = COUNT(1) from#Temp; Set @i = 1; --Circular Staging Table     while(@count >= @i)       begin          Select @account =Account from#Temp whereRow= @i;--get the blogger account for the current line          Select @articleCount = Count(1) fromBlogforbloggerwhereAccount= @account;--get the number of blog posts          UpdateBloggerSetArticlecount= @articleCount;--Update Blogger's number of articles          Set @i = @i + 1; End        Drop Table#Temp;--Delete temporary tableENDGO

This article is original article, reprint please indicate the source! My blog address: http://www.cnblogs.com/txwd

Using temporary tables to cycle data during [SQL Server] Storage