Help with cursor - SQL Server Forums

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 2005 Forums

Transact-SQL (2005)

Help with cursor

Author

Topic

smithsf22
Starting Member

9 Posts

Posted - 2009-12-03 : 13:07:05

Hello,
I have 2 tables with location data. I Need to update table one with lat\long data from table 2 matching on postal codes. What I have works but it takes about 4 hours to run. Is there a way I can speed this up or do without a cursor?

Here is the proc, not much to it


SET NOCOUNT ON;	

--get all of the postal codes we will be using
DECLARE lat_long_cursor CURSOR FOR
SELECT distinct  PostalCode 
FROM dbo.CharityListing
    
OPEN lat_long_cursor	
  FETCH NEXT FROM lat_long_cursor
  INTO @PostalCode
  
  WHILE @@FETCH_STATUS = 0
  BEGIN		
    
   UPDATE dbo.CharityListing
    SET latitude = (SELECT LATITUDE FROM dbo.PostalCodeWorldImport  WHERE POSTAL_CODE = @PostalCode), longitude= (SELECT LONGITUDE FROM dbo.PostalCodeWorldImport WHERE POSTAL_CODE = @PostalCode)WHERE PostalCode = @PostalCode
    
    FETCH NEXT FROM lat_long_cursor
    INTO @PostalCode
    END
  CLOSE lat_long_cursor
  DEALLOCATE lat_long_cursor

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2009-12-03 : 13:32:53

UPDATE a
SET a.latitude = b.Latitude
,a.Longitude = b.Longitude
from
dbo.CharityListing a
Inner Join
(Select Distinct aa.PostalCode,aa.Longitude,aa.Latitude
from dbo.PostalCodeWorldImport aa) b
on a.PostalCode = b.PostalCode

Success is 10% Intelligence, 70% Determination, and 22% Stupidity.
\_/ _/ _/\_/ _/\_/ _/ _/- 881

smithsf22
Starting Member

9 Posts

Posted - 2009-12-03 : 14:19:46

WOW thanks, down to 24 seconds

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources