Dealing with a merged record table

[lsmyth1717]

I've got a real brain teaser which I'm trying to work out. Basically I recieve a merge table in every night which supplies a list of urns which have been merged into other urns. I'm using a .net application to search on urns and return results but when a user input a urn my stored procedures looks up the merge table below and sees whether the urn exists and if it does then it goes to the tourn and sees whether another entry of it exists in the table and it works its way through until it arrives at the last tourn and it displays the results based on that urn.

My problem is that i need to jump about a lot to get to the right urn. For example based on below: -
Could go from 100 - 200, then 200 -300 and finally 300 -400 before I get the correct urn to display.

Ideally i'd like to create a new table shown below as move table which holds the final to-urn for each instance and no jumping about is required. I think the table below helps to describe what I am trying to achieve.

My problem is I am having difficulty coming up with an insert statement or algorithim which does the insert from Merge table to Move table. Can anyone please help me with this as i'm not to hot with sql.

Merge Table
MergeFromURN MergeToURN MergeDateMerged
100 200 15/06/1982
200 300 15/06/1982
300 400 15/06/1982
500 600 15/06/1982
700 100 15/06/1982

Move Table
MoveFromURN MoveFromURN MoveDateMerged
100 400 15/06/1982
200 400 15/06/1982
300 400 15/06/1982
500 600 15/06/1982
700 400 15/06/1982

 

[jbenson001]

So basically what you are saying is that you would like to create a table that holds the most current URN, based of the MoveDateMerged column?

 

[lsmyth1717]

Yeah basically thats what i'd like to do. Can you help

 

[jbenson001]

I do somthing like this currently in an app. Have an insert trigger on your main table that inserts into your move table.

First in the move table, look for the record that exists for that URN(you need some type of ID column) and delete it IF it exists.

Second, Insert the new row that was inserted into the main table.

This way only one row exists per(your ID) what ever that may be.

 

[vongrunt]

Sample data (source table):

create table myTable(MergeFromURN int, MergeToURN int, MergeDatemerged smalldatetime)
set dateformat dmy

insert into myTable values(100, 200, '15/06/1982')
insert into myTable values(200, 300, '15/06/1982')
insert into myTable values(300, 400, '15/06/1982')
insert into myTable values(500, 600, '15/06/1982')
insert into myTable values(700, 100, '15/06/1982')

And then...

select MergeFromURN as MoveFromURN, MergeToURN as MoveToURN, MergeDateMerged as MoveDateMerged
into #blah
from myTable

while @@ROWCOUNT > 0
begin
	update A set MoveToURN= B.MoveToURN
	from #blah A
	inner join #blah B on A.MoveToURN=B.MoveFromURN
end

select * from #blah
drop table #blah

Is that what ya wanted?

------
"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)