October 16, 2012 at 7:01 am
I have a scenario in which I have a table citymaster(cityid,cityname,stateid) which needs to be getupdate from statemaster (stateid,statename,statecode). I dont have common fields fields in both these tables so used another table Zipcode (cityname,statecode,zipcode,zipcodeid).
I created a cursor through which I make join on tables (state,zipcode) and (city,zipcode).
when i ran this its taking a too much time around 15 minutes and still executing...
this is the cursor below -
DECLARE @fcIty varchar(40)
DECLARE @fstate varchar(20)
declare db_cursor cursor for select fcity,fstate from WCDentalSQL_TOR..ZipCode
open db_cursor
fetch next from db_cursor into @fcity,@fstate
while @@FETCH_STATUS=0
begin
UPDATE C
SET C.STATEID =S.STATEID
FROM StateMaster S
INNER JOIN
WCDentalSQL_TOR..ZipCode Z ON Z.fState COLLATE Latin1_General_CI_AI = S.StateCode COLLATE Latin1_General_CI_AI
INNER JOIN
CityMaster C ON C.CityName COLLATE Latin1_General_CI_AI = Z.fCity COLLATE Latin1_General_CI_AI
WHERE Z.fState=@fstate
end
please suggest any suggestion on this so that it takes less time to execute..
waiting for your replies
_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
October 16, 2012 at 8:02 am
Post removed...this is a duplicate post.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
October 16, 2012 at 8:05 am
Duplicate post. Answers to http://www.sqlservercentral.com/Forums/FindPost1373217.aspx please.
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
October 16, 2012 at 8:05 am
Post removed...this is a duplicate post.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply