今天在处理历史数据的更新,需要更新100W以上的数据,开始的时候,尝试直接写SQL 语句更新,后来发现这种更新会导致死锁,最终导致更新失败。于是自己采用SQL 循环更新的方式对数据进行更新,避免了数据死锁的问题。脚本如下:
DECLARE @n AS INT --数据的循环次数
DECLARE @rows AS INT --次更新的行数
SET @rows=5000
SET @n=(SELECT COUNT(*) FROM dbo.EmployeeCompany WHERE ISNULL(InitialWpctgy,'')='')/@rows+1
WHILE @n>0
BEGIN
UPDATE t SET InitialWpctgy=ISNULL(wpctgy,''),InitialTrydat=ISNULL(trydat,'')
FROM dbo.EmployeeCompany t WHERE ISNULL(InitialWpctgy,'')=''
AND emplid IN (SELECT TOP (@rows) emplid from EmployeeCompany e WHERE ISNULL(InitialWpctgy,'')='')
SET @n=@n-1
PRINT @n
END