Update in a Table: best Approach

  • Hey Masters

    I want to update a SQL Server 2000 table.

    I have two options :

    1. Use direct SQL Update statement

    2. Use Cursors to Update the Table

    I cannot use Option 1: Reason is the Table has about 17 million records and when I am using option 1then it hangs up and due to which Other user/applications can't update the table, which I don't want.

    So I want to use Option 2. Also if any body provide me the best practice to implement Option 2 then that would be helpful.

    Please suggest, is there any other way to do this. Also If any body can provide me a sample stored procedure then that would be helpful.

  • This is a little script I keep close by. from time to time it comes in handy, esp if you need to audit the updated information, or do some calculations etc.. It is not the best performing method, but it gives you control over updated records.

    I've used this a couple of days ago, and 200 000 lines updated in less than a minute

    change it as you see fit.

    --temp table for debugging or auditing

    CREATE TABLE #Updated(

    [ITNO] [varchar](15) NULL,

    [SPRICE1] [numeric](10, 2) NULL,

    )

    Declare @Item as varchar(15)

    Declare @SPRICE1 as Numeric(10,2)

    Declare UpdatePrice

    cursor for

    (select ITNO

    from BOBS.dbo.ITEM)

    open UpdatePrice

    fetch Next

    From UpdatePrice

    Into @Item

    While @@fetch_status = 0

    begin

    print @item --For Debugging

    set @SPRICE1 = null

    Select @SPRICE1 = cast(SPRICE1 as Numeric(10,2)) from ITEM where ITNO = @Item

    update PartsSystem.dbo.ITEM

    Set

    SPRICE1 = @SPRICE1

    Where ITNO = @Item

    INSERT INTO #Updated

    ([ITNO]

    ,[SPRICE1])

    (Select ITNO, SPRICE1, from PartsSystem.dbo.ITEM Where ITNO = @Item)

    Fetch Next From UpdatePrice

    Into @item

    end

    Close UpdatePrice

    Deallocate UpdatePrice

    select * from #Updated

    --Insert your auditing checks againsts the #Updated table here

    drop table #Updated

  • Vikas,

    what is your exact requirements....??

    I would prefer to go with 1st option if the update statement doesn't require more condition wise update.

    Cheers!

    Sandy.

    --

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

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