批量分批更新数据库脚本

/*
Create table Temp_All(
	id bigint not null,
	flag tinyint default 0 not null
)
CREATE INDEX IX_number ON Temp_All(id,flag)
*/

SET NOCOUNT ON

if(isnull(object_id('Temp_Update'),0)>0)
	drop table [Temp_Update]
CREATE TABLE [Temp_Update] (
	id BIGINT NOT NULL,
	type [varchar](100),
	subtype [varchar](100)
)

DECLARE @totalnum BIGINT
DECLARE @pagenum BIGINT
DECLARE @currentpage BIGINT
DECLARE @pagesize BIGINT
SET @currentpage = 0
SET @pagesize = 500
SELECT @totalnum = COUNT(id) FROM Temp_All jtst WHERE flag=0
if @totalnum % @pagesize = 0
	SET @pagenum = @totalnum / @pagesize
ELSE
	SET @pagenum = (@totalnum / @pagesize)+1

WHILE( @currentpage < @pagenum )
BEGIN

	SET @currentpage = @currentpage+1

	BEGIN TRANSACTION
	BEGIN TRY
	
		INSERT INTO [Temp_Update]
		SELECT id,type,subtype
		FROM (
		SELECT id,type,subtype,ROW_NUMBER() OVER (ORDER BY id) AS rowid
		FROM Temp_All jtst(NOLOCK) WHERE flag=0
		) AS t
		WHERE t.rowid BETWEEN ((@currentpage-1)*@pagesize)+1 AND @currentpage*@pagesize
		ORDER BY t.rowid
	
		/* 数据库更新操作 */
		
		UPDATE [Temp_All]
		SET flag = 1
		FROM [Temp_All] jts,[Temp_Update] jtt
		WHERE jts.id = jtt.id 
			
		TRUNCATE TABLE [Temp_Update]
	
	END TRY 
	BEGIN CATCH  
	    /*输出出错信息*/
	    SELECT 
		ERROR_NUMBER() AS ErrorNumber,
		ERROR_LINE() as ErrorLine, 
		ERROR_MESSAGE() as ErrorMessage

	    IF @@TRANCOUNT > 0 
	    BEGIN
	    	ROLLBACK TRANSACTION
	    END			
	END CATCH
	IF @@TRANCOUNT > 0  
	BEGIN
		COMMIT TRANSACTION
	END
END
SET NOCOUNT OFF