July 18, 2008 at 4:27 pm
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.
July 22, 2008 at 12:54 am
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
July 22, 2008 at 1:04 am
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