Complex Sp insert question

[dfwcharles]

Hi,

I ‘m trying to create a stored procedure with two variables that will do a simple insert into TableOne, and then insert into TableTwo based on the values inserted into Tableone, plus values that were pre-existing in Tableone.
Heres an example

TableOne:

location parent
----------------- -----------------
105-01A0G-FDR01 NULL
105-01A0G-FDR02 NULL
105-01A0G-TRN01 105-01A0G-FDR01
105-01A0G-TRN01 105-01A0G-FDR02
105-01A0G-BRK01 105-01A0G-TRN01

TableTwo:

location ancestor
----------------- ----------------
105-01A0G-BRK01 105-01A0G-BRK01
105-01A0G-BRK01 105-01A0G-FDR01
105-01A0G-BRK01 105-01A0G-FDR02
105-01A0G-BRK01 105-01A0G-TRN01
105-01A0G-FDR01 105-01A0G-FDR01
105-01A0G-FDR02 105-01A0G-FDR02
105-01A0G-TRN01 105-01A0G-FDR01
105-01A0G-TRN01 105-01A0G-FDR02
105-01A0G-TRN01 105-01A0G-TRN01

When I insert these values into tableone, @location = ‘105-01A0G-BRK01’ and @parent= ‘105-01A0G-TRN01’

The sp needs to insert the following rows into Tabletwo:
Location = @location, ancestor = @location
Location = @location, ancestor = select parent from tableone where location=@parent (original parent)
Location = @location, ancestor = select parent from tableone where location=(parent of parent of original parent)
Location = @location, ancestor = select parent from tableone where location=(parent of parent of parent of original parent)

This needs to continue until the parent = null in Tableone.
I tried using simple select statements with variables in the sp, but depending on the number of parents in tableone, you can return more than one value for those varibables, which of course wont work.

I hope I explained my problem clearly, I’m having a hard time understanding it myself.

Thanks in advance.
Charles

 

[rt63]

Here is the procedure code


create procedure UrProcedure (@p1 varchar(20) , @p2 varchar(20)) as
begin

insert into TableOne values (@p1,@p2)
if not exists (select * from TableTwo where location = @p1 and ancestor = @p1)
insert into TableTwo values (@p1,@p1)

while exists (select * from TableOne
where parent is not null
and location in
(select ancestor from TableTwo
where location = @p1)
and parent not in
(select ancestor from TableTwo
where location = @p1))
begin
insert into TableTwo
select @p1 , parent
from TableOne
where parent is not null
and location in
(select ancestor from TableTwo where location = @p1)
and parent not in
(select ancestor from TableTwo where location = @p1)
end
end



This will do what u require but I am not sure whether the design is right. For e.g., u have inserted the five records in table one as stated in the problem and later u add say the following 2 records in table one

1. 105-01A0G-FDR03 null
2. 105-01A0G-TRN01 105-01A0G-FDR03

Now probably as per your needs, FDR03 should also go as an ancestor for BRK01, but it will not happen as u r not running the procedure for BRK01 again! Maybe the sequence of inserts in TableOne is well monitored - just a thought that there could be a problem...

RT

 

[dfwcharles]

Thanks RT, Thats just what I needed.

Your observation was also correct, but this sp is just for a one time data load. Any updates or additions later on would be handled by the front end of the application.

Thanks again for your help.

Charles