sql server 如何批量update数据

select @pagesize=100000, @pages=1

-- 1 prepare data list CREATE TABLE #Tbl_Affected_ID ( id INT NOT NULL, rowNum INT NOT NULL, modified CHAR(1) DEFAULT 'N' NOT NULL )

INSERT #Tbl_Affected_ID(id, rowNum) SELECT aa.id, ROW_NUMBER() OVER(ORDER BY aa.id) AS rowNum FROM TBLxxx aa WHERE userId is null '?'

-- 2 batch update WHILE exists( select 1 from #Tbl_Affected_ID where modified = 'N') BEGIN

select @offset = (@pages-1) * @pagesize select @maxresult = @offset + @pagesize

begin tran UPDATE TBLxxx SET userId = 'test_user' from #Tbl_Affected_ID tmp WHERE tmp.id = TBLxxx.id and rowNum between @offset and @maxresult

UPDATE #Tbl_Affected_ID SET modified = 'Y'

WHERE rowNum between @offset and @maxresult commit tran select @pages = @pages+1

END