July 13, 2005 at 7:36 am
I have a problem that I have already solved but I am wondering if there is a better way.
What I have: A table of organizational hierarchy data. Every record (of the 2000) reports to another record. The hierarchy starts at the top with record 1. The index numbers jump around so while 2 may report to 1, record 10 may report to record 200, record 200 may point to 201, etc.
What I need: A recordset/dataset of this data in hierarchy order starting at record 1:
My solution: Recursively call a stored procedure and store the information as I get it.
My problem: To solve this I have to drop/create a SQL server table every time. Temp tables do not work well on recursive stored procedure calls. So in my .NET code I first call the stored procedure which drops my PermaTemp (my new word) table, and rebuilds it. Then I make a second call to the database to get back my new PermTemp table.
Is there a way I can do this in one pass and w/o having to delete/define a table every time?
My proc:
CREATE PROC usp_returnStationHierarchy
@station_num int,
@depthlevel int
AS
declare @parentid int
declare @stationname varchar(100)
if @depthlevel = 0
begin
drop table dbo.StationHierarchyView
create table dbo.StationHierarchyView ( station_num int , parentid int , stationname char (100) )
insert into dbo.StationHierarchyView select facilityid, parentstationid, aliasacronym + '/' + stationformalname from Station where facilityid = 1
update dbo.StationHierarchyView set parentid = null where station_num = 1
end
select @depthlevel = @depthlevel + 1
declare cur_level cursor local for
select facilityid, parentstationid, aliasacronym + '/' + stationformalname from Station
where parentstationid = @station_num and parentstationid <> facilityid
open cur_level
fetch next from cur_level into @station_num, @parentid, @stationname
while @@fetch_status = 0
begin
insert into dbo.StationHierarchyView (station_num, parentid, stationname) values (@station_num, @parentid, @stationname)
exec usp_returnStationHierarchy @station_num, @depthlevel
fetch next from cur_level into @station_num, @parentid, @stationname
end
close cur_level
deallocate cur_level
Patrick Allmond
batteryfuel.com
For all of your laptop, camera, power tool and portable battery needs.
July 13, 2005 at 8:24 am
Rather than DROP/CREATE, why don't you just TRUNCATE the table? Not that that will save much time though ...
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
July 13, 2005 at 8:33 am
Thanks for the suggestion.
For some reason I thought I had tried that and what happens is the records do disappear. But tne new table does not have the records in the order that they were inserted it. It was explained to me that when you truncate the records do not truly disappear and you get some fragmentation.
Patrick Allmond
batteryfuel.com
For all of your laptop, camera, power tool and portable battery needs.
July 13, 2005 at 8:53 am
Does the insertion order matter? Can't you just create a clustered primary key to force the display order?
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
July 13, 2005 at 8:57 am
The insertion order matters because when I read the data back in my hierarchy information is built/displayed starting from the top on down.
Clustered Primary Key? Would that be like adding a new column while I am iterating and the new column would be the display order?
Patrick Allmond
batteryfuel.com
For all of your laptop, camera, power tool and portable battery needs.
July 14, 2005 at 1:55 am
If the hierarchy is definitely that one item can only have one parent, this is the classic problem of hierarchical data in a flat table. There are a number of ways to solve it and depending on your tables' update frequency versus retrieval frequency your recursive solution is perfectly valid. There is however a very fast, one pass solution that solves this problem but is geared towards a more retrieval orientated situation called "modified preorder tree traversal".
The excellent article that I learnt this method from (http://www.sitepoint.com/article/hierarchical-data-database/) seems no longer available but Joe Celko has written a book which covers this technique and I'm sure there are code samples out there somewhere.
If you struggle to track anything down, let me know and I'll dig an example out which may help explain.
July 14, 2005 at 6:53 am
You want to read this thread:
http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=8&messageid=175525#bm188799
This is a much more complicated problem than even you imagine.
On the Clustered Primary Key: If the insertion order matters then add a column at the beginning of the table (i.e. IDKEY) as int, set it as an Identity column and then set it as a Clustered Primary Key. Each entry will generate an IDKEY, starting with 1 and incrementing by 1. If you then retrieve using the PK you get them in sequence. The Truncate will reset the base Identity value to 1.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply