Post reply

commiting transactions

  • November 30, 2004 at 12:59 pm

    #66646

    Is there a way to commit transactions in an insert after say 10,000 rows are selected?

  • November 30, 2004 at 1:29 pm

    #531949

    Could you use a cursor to count rows?

    Kindest Regards,

    Sean Wyatt
    seanwyatt.com

  • November 30, 2004 at 1:34 pm

    #531954

    put it in a while loop that checks @@rowcount

    use SET ROWCOUNT 10000

     

     

  • November 30, 2004 at 2:25 pm

    #531969

    could you give me a short query that shows this loop

     

  • December 1, 2004 at 7:27 am

    #532050

    You'll need something like this

    first create a new field that flags the updated rows

    then updated the rows that are not flaged as updated.

    I keep the rowcount in @MyRowCount because when I do set@i =

    @@rowcount is reset to 0

    use documentation

    alter table ObjSQL

    ADD tmp tinyint null

    go

    Declare @i as int

    Declare @MyRowCount as int

    Declare @BatchSize as int

    set @BatchSize = 1000

    set @i = 1

    SET ROWCOUNT @BatchSize

    Update ObjSQL set tmp = 1 where tmp is null

    set @MyRowCount = @@rowcount

    while @MyRowCount = @BatchSize

    begin

    Update ObjSQL set tmp = 1 where tmp is null

    set @MyRowCount = @@rowcount

    set @i = @i + 1

    end

    print @i

    alter table ObjSQL Drop column tmp

    go

    SET ROWCOUNT 0

Viewing 5 posts - 1 through 4 (of 4 total)

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