need help on CURSOR programming

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

Transact-SQL (2000)

need help on CURSOR programming

Author

Topic

kanna
Starting Member

1 Post

Posted - 2010-06-10 : 14:44:34

Hi, Every day I need to update ToTable depends on FromTable. Depends on 'NUM & NAME' fields this has to be updated.
Each row in the FromTable has to be compared with each row in the ToTable, while comparing, if the same row exist (depends on 'NUM & NAME' fields), ToTable has to be updated if not it has to be inserted as a new row in ToTable.

Here is the code that I am using but this code is not at all helping me out, code is never going to be end, its keep on running:

SET NOCOUNT ON

DECLARE @ToName VARCHAR(50)
DECLARE @ToNum INT
DECLARE @ToArea VARCHAR(24)
DECLARE @ToJoinDay VARCHAR(24)

DECLARE @rowName VARCHAR(50)
DECLARE @rowNum INT
DECLARE @rowArea VARCHAR(24)
DECLARE @rowjoinDay VARCHAR(24)

DECLARE stagingCursor CURSOR READ_ONLY FOR
SELECT
name,num,place,Area,Joinday from dbo.FromTable

OPEN stagingCursor
FETCH NEXT FROM stagingCursor INTO
@rowName,@rowNum,@rowArea,@rowJoinDay

WHILE @@FETCH_STATUS = 0
BEGIN

IF (@ToNum = @rowNum and @ToName = @rowName) BEGIN
SET @ToName = @rowName
SET @ToNum = @rowNum
SET @ToArea = @rowArea
SET @ToJoinDay = @rowJoinDay
END

IF (@ToNum = @rowNum and @ToName = @rowName) BEGIN
-- Existing record. Update
UPDATE ToTable
SET name = @ToName
, num = @ToNum
, Area = @ToArea
, JoinDay = @ToJoinDay
WHERE num = @ToNum and name = @ToName END
ELSE
INSERT INTO TOTABLE(NAME,NUM,Area,JoinDay)
VALUES (@ToName,@ToNum,@ToArea,@ToJoinDay)

CLOSE stagingCursor
DEALLOCATE stagingCursor

END

looking forward
Thanks in Advance.

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-06-10 : 15:06:07

There's no need for cursor.

Have a look at this
http://weblogs.sqlteam.com/mladenp/archive/2007/07/30/60273.aspx

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources