Help required using a cursor

[lsmyth1717]

I have a query about writing code to use a sql server to loop through a record set in one table and then depending on the record insert a row into a new table. Each night I will receive a merge table file with new values in it. Depending on these values I will want to insert a record across to a new Table called Move. Some nights I might want to update values in Move based on a new row which I'll receive in the Merge Table. Below is a working example of what I have to do. Can someone please help me finish writing the code for the cursor.

MONDAY NIGHT
MERGE TABLE
From To
100 200
200 300
Need this to be inserted into Move as follows: -
MOVE TABLE
From To
100 300
200 300
(As you can see the from row with 100 has had its to value updated)

TUESDAY NIGHT
MERGE TABLE
From To
300 100
Don't want to bring this row accross because 300 already exists as a ToURN in the Move table so want to ignore this row and not insert it.
MOVE TABLE
From To
100 300
200 300
(No exta row inserted)

WEDNESDAY NIGHT
MERGE TABLE
From To
Null 100
100 Null
Null Null
Null 0
0 100
100 0
0 0
100 300 (already exists so don't insert it)
If I get any of these combinations above I don't want to insert the record
MOVE TABLE
From To
100 300
200 300
(No exta row inserted)

THURSDAY
MERGE TABLE
From To
200 400
If I get a row with a From value the same as one which exists in the Move table and the to value for that row doesn't exist as a from value in the Move table then I would like to update the row with the correct value.
MOVE TABLE
From To
100 300
200 400
(No exta row inserted)


My starter cursor looks as follows: -
Declare @FromURN bigint
Declare @ToURN bigint

Declare MoveCursor CURSOR FOR

--This gets all the rows which don't have a null or don't have a 0 and
--MergeFromURN and MergeToURN don't exist in the Move Table

Select MergeFromURN, MergeToURN from Merge where MergeFromURN is not null and MergeToURN is not null
and MergeFromURN <> 0 and MergeToURN <> 0 and
MergeFromURN <> MergeToURN and
(MergeFromURN not in (select MoveFromURN from Move) and
MergeToURN not in (select MoveToURN from Move))
Open MoveCursor
Fetch Next From MoveCursor INTO @FromURN, @ToURN
While @@Fetch_Status = 0
BEGIN
--Never used a cursor before and am not sure what I need to do here to get my
--to code to loop around each record amd do the necessary checking and inserting
--or updating a row as appropriate.

FETCH NEXT FROM MoveCursor
INTO @FromURN, @ToURN
END
Close MoveCursor
Deallocate MoveCursor

Here our the tables I'll be using. Each night the Merge FROM URN is cleared out.
CREATE TABLE [dbo].[MERGE] (
[RecNo] [int] NULL ,
[MergeFromURN] [bigint] NULL ,
[MergeToURN] [bigint] NULL ,
[MergeDateMerged] [datetime] NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[MOVE] (
[RecNO] [bigint] NULL ,
[MoveFromURN] [bigint] NULL ,
[MoveToURN] [bigint] NULL ,
[MoveDateMerged] [datetime] NULL ,
[Timestamp] [datetime] NULL
) ON [PRIMARY]

 

[vongrunt]

Er... have you tried code from thread183-1093473?

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[lsmyth1717]

Yeah that work ok for part of the problem, however there are so many variables in place which I need to work through before I decide whether to insert or update a record that i've been told a cursor is better to use. Your code worked but when it came to things like if a row in the merge table was exactly the same as one which existed in the move table then don't insert it.
Also if a ToURN in the merge table is the same as a from urn in the move table then don't insert the row. Like in the example of Tuesday night above.

Also need to deal with Thursday nights record as well