Results 1 to 5 of 5
  1. #1
    Join Date
    Mar 2004
    Posts
    3

    Unanswered: commit every 100 records

    hi there,

    I have a sp which updates a few tousend records,but i want it to commit every 100 records, so whats the best way to do that?

    thankx

  2. #2
    Join Date
    Mar 2004
    Posts
    4

    Re: commit every 100 records

    Originally posted by dvh
    hi there,

    I have a sp which updates a few tousend records,but i want it to commit every 100 records, so whats the best way to do that?

    thankx
    IF @@ROWCOUNT = 100 COMMIT

    can you display the sp

  3. #3
    Join Date
    Mar 2004
    Posts
    25
    IF @@ROWCOUNT = 100 COMMIT
    Would this commit for every 100 records, or just commit if the update affected 100 records in total?

    thanks

  4. #4
    Join Date
    Nov 2002
    Posts
    207
    Another option would be (only if you have updated column in your where clause)...

    set rowcount 100

    while loop
    { update ....
    commit
    }

    HTH.

  5. #5
    Join Date
    Mar 2004
    Posts
    3

    Re: commit every 100 records

    create procedure SP_INDICATIE_VERWERKT_UPD

    as
    declare @teller int
    select @teller = 1

    set rowcount 0

    begin
    begin transaction
    update hbt_schema_rente_afl_kst
    set indicatie_verwerkt = '0'
    where datepart(day,datum_lamu) >= 1
    and datepart(month,datum_lamu) = 3
    if(@@rowcount = @teller)
    commit transaction
    select @@rowcount, 'rows affected'
    end

    return 0


    something like this, the general idea is that every houndred recs a commit is taking place so the log doesnt grow to large.
    thnkx

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •