/*
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
批量分批更新数据库脚本
最新推荐文章于 2023-02-08 14:14:09 发布