如何逐块更新巨大的记录

how to update the huge records chunk by chunk(如何逐块更新巨大的记录)
本文介绍了如何逐块更新巨大的记录的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着跟版网的小编来一起学习吧!

问题描述

我有大约 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.

这篇关于如何逐块更新巨大的记录的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持跟版网!

本站部分内容来源互联网,如果有图片或者内容侵犯了您的权益,请联系我们,我们会在确认后第一时间进行删除!

相关文档推荐

Query with t(n) and multiple cross joins(使用 t(n) 和多个交叉连接进行查询)
Unpacking a binary string with TSQL(使用 TSQL 解包二进制字符串)
Max rows in SQL table where PK is INT 32 when seed starts at max negative value?(当种子以最大负值开始时,SQL 表中的最大行数其中 PK 为 INT 32?)
Inner Join and Group By in SQL with out an aggregate function.(SQL 中的内部连接和分组依据,没有聚合函数.)
Add a default constraint to an existing field with values(向具有值的现有字段添加默认约束)
SQL remove from running total(SQL 从运行总数中删除)