commiting transactions

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

  • Could you use a cursor to count rows?


    Kindest Regards,

    Sean Wyatt
    seanwyatt.com

  • put it in a while loop that checks @@rowcount

    use SET ROWCOUNT 10000

     

     

  • could you give me a short query that shows this loop

     

  • 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