Alternate Using Cursors to Update

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Alternate Using Cursors to Update

Author

Topic

praveen2006
Starting Member

2 Posts

Posted - 2011-05-17 : 03:09:43

I have 2 tables - Table 1(Temp table), Table 2(Main Table)

I need to fetch few records from Table1 and Update or Insert into Table2.... Table 2 already as 5 million records in DB. Table1 has around 20000 records where i need to search and update...

So i used Cursor where i fetch each record one by one search in table 2 and update..... and it works perfectly.....

But actually there were 3000 available records but to update the 3000 records into Table 2 it takes 1 hr to update those records. Here execution time is very huge......

So can one tell me what will alternate method to use instead of using Cursors.....

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-05-17 : 03:21:11

[code]
update t2
set somecol = t1.somecol
from table1 t1 inner join table2 t2 on t1.pk = t2.pk

insert into table2 ( <column list> )
select <column list>
from table1 t1
where not exists
(
select * from table2 x where x.pk = t1.pk
)
[/code]

KH
[spoiler]Time is always against us[/spoiler]

praveen2006
Starting Member

2 Posts

Posted - 2011-05-18 : 03:00:47

Below is the Code

IND2802201114_Catalog --> Table 1 --> which as around 10000 rows

ng_catalog..productcatalog2 --> Table 2 --> which as around 5 million rows

I need to check isbn is available or not, if available update in Table 2 or insert in Table 2

-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --

DECLARE CURProduct CURSOR FAST_FORWARD FOR

select [ISBN13],Price,[Name],[Author1],[Currency],[Publisher],[BindingType] from IND2802201114_Catalog where Stock >=5 and DateModified >= convert(varchar,getdate(),101)

OPEN CURProduct
FETCH Next FROM CURProduct INTO @ISBN,@price,@title,@author,@curr,@publishername,@binding

While @@FETCH_STATUS=0
Begin

set @listPrice = round(CONVERT(money,@Price) * CONVERT(money,@US),0)

/* Checking Whether The ISBN Is Available In Product Catalog Table.... */

if exists (select isbn from ng_catalog..productcatalog2 where ISBN=@ISBN)
Begin

/* Checking Whether The ISBN Is Available In Supplier Table.... If True - Update ISBN Into Supplier Table */

if exists (select sku from ng_catalog..suppliersku2 where sku=@ISBN and SPID=@vendorid)
Begin
Update ng_catalog..suppliersku2 set
[List Price] = round(@listPrice,0),
[Buy Price] =round((@listPrice * (100-@margin)/100),0),
[Currency Value] =CONVERT(money, @price),
[Currency Type] = @CurrencyType,
LastModified = getdate(),
[bookRsValue] =round(@listPrice,0),
[Item Qty] = 1,
[Item Status] = 1
where sku = @isbn and SPID=@vendorid
End

Else

/* If The ISBN Is Not Available In Supplier Table, Control Comes Hear.... Insert ISBN Into Supplier Table */

Begin

-- Select @publisherId = publisher from ng_catalog..productcatalog2 where sku=@ISBN and publisherid is not null
-- Select @priority = Priority from ng_catalog..PublisherVendorPriority where PublisherId = @publisherId

Insert into ng_catalog..suppliersku2(Store_id,sku,[MFR Item Code],[Buy Price],[List Price],[Item Status],[Item Qty],[Currency Value],
[Currency Type],[UpdatedBy],LastModified,[BookRsValue], SpId) Values

(2,@isbn,@isbn,(@listPrice * (100-@margin)/100), @listPrice,1,1, CONVERT(money, @price),
@CurrencyType, 'New Supplier', getdate(), @listPrice, @vendorid)

-- Print 'Supplier Insert Successfully Completed'
End
End

FETCH NEXT FROM CURProduct INTO @isbn,@price,@title,@author,@curr,@publishername,@binding
End

Close CURProduct
Deallocate CURProduct

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources