問題描述

我有大約 500 000 的數(shù)據(jù).我需要將 First name 和 Second name 的數(shù)據(jù)更新為大寫

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

我的樣本數(shù)據(jù):

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 

當(dāng)數(shù)據(jù)大約為 10 000 條記錄時(shí)它工作正常，但是當(dāng)我嘗試更新大約 500 萬(wàn)條記錄時(shí)系統(tǒng)掛斷了.如何像 10000 和 10000 那樣逐塊更新，以免系統(tǒng)負(fù)擔(dān)?

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

您可以根據(jù)需要更改上述代碼.如果這有幫助，請(qǐng)告訴我.

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

這篇關(guān)于如何逐塊更新巨大的記錄的文章就介紹到這了，希望我們推薦的答案對(duì)大家有所幫助，也希望大家多多支持html5模板網(wǎng)！