如何逐块更新巨大的记录

问题描述

我有大约 500 000 的数据.我需要将 First name 和 Second name 的数据更新为大写

I have data around 500 000. I need to update the data of First name and Second name to capital letters

我的样本数据:

declare @T table(Firstname varchar(max),Secondname varchar(max))
insert into @T values ('ch bhaskar rao ','sridhar kolla ')
insert into @T values ('hemanth kumar','varun chenna-reddy')
insert into @T values ('mohan vara prasad','raju t d p durga raju')
insert into @T values ('police mutual','police mutual')

我的脚本:

;WITH CTE AS (
    select (
        select upper(T.N.value('.', 'char(1)')),
            lower(stuff(T.N.value('.', 'varchar(max)'), 1, 1, ''))+(CASE WHEN RIGHT(T.N.value('.', 'varchar(max)'), 1)='-' THEN '' ELSE ' ' END)
        from X.Secondname.nodes('/N') as T(N)
        for xml path(''), type
    ).value('.', 'varchar(max)') As Secondname
    from 
    (
        select 
         cast('<N>'+replace(replace(replace(Secondname, ' ', '</N><N>'),' ', '</N><N>'),'-','-</N><N>')  +'</N>' as xml) as Secondname
        from @T
    ) as X 
)
UPDATE T 
SET
    Secondname = C.Secondname
FROM 
    CTE C 
    INNER JOIN @T T 
        ON   T.Secondname = C.Secondname

Select Secondname from @T 

当数据大约为 10 000 条记录时它工作正常，但是当我尝试更新大约 500 万条记录时系统挂断了.如何像 10000 和 10000 那样逐块更新，以免系统负担?

It is working fine when data is around 10 000 records but system getting hanged up when I'm trying to update huge records around 5 millions. How to update chunk by chunk like 10000 and again 10000, so that system will not burdened?

推荐答案

您可以像下面提到的那样分块更新表格:

You can update the table in chunks like mentioned below:

declare @i int = 2
declare @j int = 1

    while @j > 0 
        begin 
            begin transaction
            update top (@i) a
            set a.firstname = upper(a.firstname),
                a.secondname = upper(a.secondname)
            from aa as a
            where a.firstname collate latin1_general_cs_as <> upper(a.firstname)
            set @j = @@rowcount
            commit transaction

        end

您可以根据需要更改上述代码.如果这有帮助，请告诉我.

you can change the above code as per your needs. Let me know if this helps.