Post reply

Commit Every X Rows When Delete

  • November 27, 2003 at 5:39 pm

    #82872

    I want to delete from a "large" table (more than 1,000,000 records) with conditions. Even with conditions, I still need to delete out around 500,000 records.

    If I just execute delete statement like below, it take long time and has possibility timeout (transaction log also grow very fast). Can I commit for every 5,000 records or less?

    Edited by - kokyan on 11/27/2003 6:28:16 PM



    Regards,
    kokyan

  • November 28, 2003 at 1:55 am

    #483968

    Here's a crude (no error checking) bit of code to demonstrate a loop to do it:

     

    

    set rowcount 5000

    declare @rows int

    while 1 = 1

        begin    

            begin tran

            delete <Yourtable> where <condition>

            select @rows = @@rowcount

            commit tran

            if @rows = 0 break

        end

    Cheers,

    - Mark


    Cheers,
    - Mark

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply