Copying a hierarchy of rows

Hi,
I have this scenario:
Table of Folders: FolderID, FolderName, ParentFolderID
I want to be able to *copy* a whole hierarchy of Folders, i.e. my stored
procedure gets three parameters: SourceFolderID, ParentFolderID,
NewFolderName. It creates a new folder whose name and parent are taken
from the parameters. Now comes the hard part - take all the descendant
folders of SourceFolderID and insert new rows with the same hierarchy
under the new folder. The hierarchy is not limited to one level ofcourse...
I'm looking for a solution on SQL Server 2000.
Anyone? :-)
Thanks in advance,
R. GreenRonald
Google for Itzik Ben-Gan's solutions for such kind of query
There are some examples from his articles
CREATE DATABASE testdb
GO
USE testdb
GO
-- Schema and Data of the Employees table
CREATE TABLE Employees
(
empid int NOT NULL,
mgrid int NULL,
empname varchar(25) NOT NULL,
salary money NOT NULL,
CONSTRAINT PK_Employees_empid PRIMARY KEY(empid),
CONSTRAINT FK_Employees_mgrid_empid
FOREIGN KEY(mgrid)
REFERENCES Employees(empid)
)
CREATE INDEX idx_nci_mgrid ON Employees(mgrid)
INSERT INTO Employees VALUES(1 , NULL, 'Nancy' , $10000.00)
INSERT INTO Employees VALUES(2 , 1 , 'Andrew' , $5000.00)
INSERT INTO Employees VALUES(3 , 1 , 'Janet' , $5000.00)
INSERT INTO Employees VALUES(4 , 1 , 'Margaret', $5000.00)
INSERT INTO Employees VALUES(5 , 2 , 'Steven' , $2500.00)
INSERT INTO Employees VALUES(6 , 2 , 'Michael' , $2500.00)
INSERT INTO Employees VALUES(7 , 3 , 'Robert' , $2500.00)
INSERT INTO Employees VALUES(8 , 3 , 'Laura' , $2500.00)
INSERT INTO Employees VALUES(9 , 3 , 'Ann' , $2500.00)
INSERT INTO Employees VALUES(10, 4 , 'Ina' , $2500.00)
INSERT INTO Employees VALUES(11, 7 , 'David' , $2000.00)
INSERT INTO Employees VALUES(12, 7 , 'Ron' , $2000.00)
INSERT INTO Employees VALUES(13, 7 , 'Dan' , $2000.00)
INSERT INTO Employees VALUES(14, 11 , 'James' , $1500.00)
GO
-- Scalar functions --
--
-- Getting Ancestor using Recursion
CREATE FUNCTION dbo.ufn_GetAncestor
(
@.empid AS int,
@.lvl AS int = 1 -- levels above employee
)
RETURNS int
AS
BEGIN
IF @.lvl IS NULL or @.empid IS NULL or @.lvl < 0
RETURN NULL
IF @.lvl = 0
RETURN @.empid
RETURN dbo.ufn_GetAncestor(
(SELECT mgrid FROM Employees WHERE empid = @.empid),
@.lvl -1)
END
GO
-- Getting Ancestor using a Loop
CREATE FUNCTION dbo.ufn_GetAncestor2
(
@.empid AS int,
@.lvl AS int = 1 -- levels above employee
)
RETURNS int
AS
BEGIN
IF @.lvl IS NULL or @.empid IS NULL or @.lvl < 0
RETURN NULL
DECLARE @.mgrid AS int
SET @.mgrid = @.empid
WHILE @.lvl > 0 AND @.mgrid IS NOT NULL
SELECT @.mgrid = mgrid, @.lvl = @.lvl - 1
FROM Employees WHERE empid = @.mgrid
RETURN @.mgrid
END
GO
-- test the dbo.ufn_GetAncestor function
SELECT dbo.ufn_GetAncestor(11, 2)
SELECT * FROM Employees WHERE empid = dbo.ufn_GetAncestor(11, 2)
SELECT E.empname AS employee, A.empname AS ancestor
FROM Employees AS E LEFT OUTER JOIN Employees AS A
ON A.empid = dbo.ufn_GetAncestor(E.empid, 2)
GO
-- Calculating an Aggregate of a Subtree
CREATE FUNCTION dbo.ufn_GetSubtreeSalary
(
@.mgrid AS int
)
RETURNS int
AS
BEGIN
RETURN (SELECT Salary
FROM Employees WHERE empid = @.mgrid) +
CASE
WHEN EXISTS(SELECT * FROM Employees WHERE mgrid = @.mgrid) THEN
(SELECT SUM(dbo.ufn_GetSubtreeSalary(empid))
FROM Employees
WHERE mgrid = @.mgrid)
ELSE 0
END
END
GO
SELECT dbo.ufn_GetSubtreeSalary(3)
GO
-- Calculating the Depth of a Subtree
CREATE FUNCTION dbo.ufn_GetSubtreeDepth
(
@.mgrid AS int
)
RETURNS int
AS
BEGIN
RETURN CASE
WHEN EXISTS(SELECT * FROM Employees WHERE mgrid = @.mgrid) THEN
1 + (SELECT MAX(dbo.ufn_GetSubtreeDepth(empid))
FROM Employees
WHERE mgrid = @.mgrid)
WHEN EXISTS(SELECT * FROM Employees WHERE empid = @.mgrid) THEN 1
ELSE NULL
END
END
GO
SELECT dbo.ufn_GetSubtreeDepth(1)
GO
-- Table-Valued functions --
--
-- Get a whole Subtree
CREATE FUNCTION ufn_GetSubtree
(
@.mgrid AS int
)
RETURNS @.tree table
(
empid int NOT NULL,
mgrid int NULL,
empname varchar(25) NOT NULL,
salary money NOT NULL,
lvl int NOT NULL,
path varchar(900) NOT NULL
)
AS
BEGIN
DECLARE @.lvl AS int, @.path AS varchar(900)
SELECT @.lvl = 0, @.path = '.'
INSERT INTO @.tree
SELECT empid, mgrid, empname, salary,
@.lvl, '.' + CAST(empid AS varchar(10)) + '.'
FROM Employees
WHERE empid = @.mgrid
WHILE @.@.ROWCOUNT > 0
BEGIN
SET @.lvl = @.lvl + 1
INSERT INTO @.tree
SELECT E.empid, E.mgrid, E.empname, E.salary,
@.lvl, T.path + CAST(E.empid AS varchar(10)) + '.'
FROM Employees AS E JOIN @.tree AS T
ON E.mgrid = T.empid AND T.lvl = @.lvl - 1
END
RETURN
END
GO
SELECT REPLICATE (' | ', lvl) + empname AS employee
FROM ufn_GetSubtree(1)
ORDER BY path
/*
employee
--
Nancy
| Andrew
| | Steven
| | Michael
| Janet
| | Robert
| | | David
| | | | James
| | | Ron
| | | Dan
| | Laura
| | Ann
| Margaret
| | Ina
*/
-- get leaf nodes of a subtree
SELECT * FROM ufn_GetSubtree(2) AS S
WHERE NOT EXISTS(SELECT * FROM Employees AS E
WHERE E.mgrid = S.empid)
GO
-- Get the Chain of Management Leading to a Certain Employee
CREATE FUNCTION ufn_GetMgmtChain
(
@.empid AS int
)
RETURNS @.tree table
(
empid int NOT NULL,
mgrid int NULL,
empname varchar(25) NOT NULL,
salary money NOT NULL,
lvl int NOT NULL
)
AS
BEGIN
DECLARE @.lvl AS int
SET @.lvl = 0
INSERT INTO @.tree
SELECT empid, mgrid, empname, salary, @.lvl
FROM Employees
WHERE empid = @.empid
WHILE @.@.ROWCOUNT > 0
BEGIN
SET @.lvl = @.lvl + 1
INSERT INTO @.tree
SELECT E.empid, E.mgrid, E.empname, E.salary, @.lvl
FROM Employees AS E JOIN @.tree AS T
ON E.empid = T.mgrid AND T.lvl = @.lvl - 1
END
RETURN
END
GO
SELECT * FROM ufn_GetMgmtChain(14)
ORDER BY lvl DESC
"Ronald Green" <b@.man.com> wrote in message
news:%23a3iMT1vFHA.2064@.TK2MSFTNGP09.phx.gbl...
> Hi,
> I have this scenario:
> Table of Folders: FolderID, FolderName, ParentFolderID
> I want to be able to *copy* a whole hierarchy of Folders, i.e. my stored
> procedure gets three parameters: SourceFolderID, ParentFolderID,
> NewFolderName. It creates a new folder whose name and parent are taken
> from the parameters. Now comes the hard part - take all the descendant
> folders of SourceFolderID and insert new rows with the same hierarchy
> under the new folder. The hierarchy is not limited to one level
> ofcourse...
> I'm looking for a solution on SQL Server 2000.
> Anyone? :-)
> Thanks in advance,
> R. Green|||Ronald
you can use dynamic sql if you want to change column names dynamically
something like this
declare @.stringvar varchar(100), @.name varchar(100), @.stno int
set @.stringvar = 'select ' + @.name + 'from pis where ' + @.stno + '= 1548'
exec(@.stringvar)
or you can use sp_executesql which has capacity to use parameters
for full discussion read this
http://www.sommarskog.se/dynamic_sql.html
however, it appers to me that it is possible to use plain sql here instead
of dynamic one. if you can post DDL/SAMPLE data we will better suggest you.
Regards
R.D
"Ronald Green" wrote:

> Hi,
> I have this scenario:
> Table of Folders: FolderID, FolderName, ParentFolderID
> I want to be able to *copy* a whole hierarchy of Folders, i.e. my stored
> procedure gets three parameters: SourceFolderID, ParentFolderID,
> NewFolderName. It creates a new folder whose name and parent are taken
> from the parameters. Now comes the hard part - take all the descendant
> folders of SourceFolderID and insert new rows with the same hierarchy
> under the new folder. The hierarchy is not limited to one level ofcourse..
.
> I'm looking for a solution on SQL Server 2000.
> Anyone? :-)
> Thanks in advance,
> R. Green
>|||oops
It is at the wrong place
"R.D" wrote:
> Ronald
> you can use dynamic sql if you want to change column names dynamically
> something like this
> declare @.stringvar varchar(100), @.name varchar(100), @.stno int
> set @.stringvar = 'select ' + @.name + 'from pis where ' + @.stno + '= 1548
'
> exec(@.stringvar)
> or you can use sp_executesql which has capacity to use parameters
> for full discussion read this
> http://www.sommarskog.se/dynamic_sql.html
> however, it appers to me that it is possible to use plain sql here instea
d
> of dynamic one. if you can post DDL/SAMPLE data we will better suggest you
.
> Regards
> R.D
>
>
> "Ronald Green" wrote:
>|||Hi Uri,
I read parts of Itzik's solutions for maintaining hierarchies. Didn't
find anything about *copying* a hierarchy... I figure that copying the
hierarchy is a bit more complicated than aggregates and such on the
hierarchy, so I decided to do the recursion from outside... a lot more
calls to the servers but it'll be faster to write.
Cheers,
R. Green
Uri Dimant wrote:
> Ronald
> Google for Itzik Ben-Gan's solutions for such kind of query
> There are some examples from his articles
>
> CREATE DATABASE testdb
> GO
> USE testdb
> GO
> -- Schema and Data of the Employees table
> CREATE TABLE Employees
> (
> empid int NOT NULL,
> mgrid int NULL,
> empname varchar(25) NOT NULL,
> salary money NOT NULL,
> CONSTRAINT PK_Employees_empid PRIMARY KEY(empid),
> CONSTRAINT FK_Employees_mgrid_empid
> FOREIGN KEY(mgrid)
> REFERENCES Employees(empid)
> )
> CREATE INDEX idx_nci_mgrid ON Employees(mgrid)
> INSERT INTO Employees VALUES(1 , NULL, 'Nancy' , $10000.00)
> INSERT INTO Employees VALUES(2 , 1 , 'Andrew' , $5000.00)
> INSERT INTO Employees VALUES(3 , 1 , 'Janet' , $5000.00)
> INSERT INTO Employees VALUES(4 , 1 , 'Margaret', $5000.00)
> INSERT INTO Employees VALUES(5 , 2 , 'Steven' , $2500.00)
> INSERT INTO Employees VALUES(6 , 2 , 'Michael' , $2500.00)
> INSERT INTO Employees VALUES(7 , 3 , 'Robert' , $2500.00)
> INSERT INTO Employees VALUES(8 , 3 , 'Laura' , $2500.00)
> INSERT INTO Employees VALUES(9 , 3 , 'Ann' , $2500.00)
> INSERT INTO Employees VALUES(10, 4 , 'Ina' , $2500.00)
> INSERT INTO Employees VALUES(11, 7 , 'David' , $2000.00)
> INSERT INTO Employees VALUES(12, 7 , 'Ron' , $2000.00)
> INSERT INTO Employees VALUES(13, 7 , 'Dan' , $2000.00)
> INSERT INTO Employees VALUES(14, 11 , 'James' , $1500.00)
> GO
> --
> -- Scalar functions --
> --
> -- Getting Ancestor using Recursion
> CREATE FUNCTION dbo.ufn_GetAncestor
> (
> @.empid AS int,
> @.lvl AS int = 1 -- levels above employee
> )
> RETURNS int
> AS
> BEGIN
> IF @.lvl IS NULL or @.empid IS NULL or @.lvl < 0
> RETURN NULL
> IF @.lvl = 0
> RETURN @.empid
> RETURN dbo.ufn_GetAncestor(
> (SELECT mgrid FROM Employees WHERE empid = @.empid),
> @.lvl -1)
> END
> GO
> -- Getting Ancestor using a Loop
> CREATE FUNCTION dbo.ufn_GetAncestor2
> (
> @.empid AS int,
> @.lvl AS int = 1 -- levels above employee
> )
> RETURNS int
> AS
> BEGIN
> IF @.lvl IS NULL or @.empid IS NULL or @.lvl < 0
> RETURN NULL
> DECLARE @.mgrid AS int
> SET @.mgrid = @.empid
> WHILE @.lvl > 0 AND @.mgrid IS NOT NULL
> SELECT @.mgrid = mgrid, @.lvl = @.lvl - 1
> FROM Employees WHERE empid = @.mgrid
> RETURN @.mgrid
> END
> GO
> -- test the dbo.ufn_GetAncestor function
> SELECT dbo.ufn_GetAncestor(11, 2)
> SELECT * FROM Employees WHERE empid = dbo.ufn_GetAncestor(11, 2)
> SELECT E.empname AS employee, A.empname AS ancestor
> FROM Employees AS E LEFT OUTER JOIN Employees AS A
> ON A.empid = dbo.ufn_GetAncestor(E.empid, 2)
> GO
> -- Calculating an Aggregate of a Subtree
> CREATE FUNCTION dbo.ufn_GetSubtreeSalary
> (
> @.mgrid AS int
> )
> RETURNS int
> AS
> BEGIN
> RETURN (SELECT Salary
> FROM Employees WHERE empid = @.mgrid) +
> CASE
> WHEN EXISTS(SELECT * FROM Employees WHERE mgrid = @.mgrid) THEN
> (SELECT SUM(dbo.ufn_GetSubtreeSalary(empid))
> FROM Employees
> WHERE mgrid = @.mgrid)
> ELSE 0
> END
> END
> GO
> SELECT dbo.ufn_GetSubtreeSalary(3)
> GO
> -- Calculating the Depth of a Subtree
> CREATE FUNCTION dbo.ufn_GetSubtreeDepth
> (
> @.mgrid AS int
> )
> RETURNS int
> AS
> BEGIN
> RETURN CASE
> WHEN EXISTS(SELECT * FROM Employees WHERE mgrid = @.mgrid) THEN
> 1 + (SELECT MAX(dbo.ufn_GetSubtreeDepth(empid))
> FROM Employees
> WHERE mgrid = @.mgrid)
> WHEN EXISTS(SELECT * FROM Employees WHERE empid = @.mgrid) THEN 1
> ELSE NULL
> END
> END
> GO
> SELECT dbo.ufn_GetSubtreeDepth(1)
> GO
> --
> -- Table-Valued functions --
> --
> -- Get a whole Subtree
> CREATE FUNCTION ufn_GetSubtree
> (
> @.mgrid AS int
> )
> RETURNS @.tree table
> (
> empid int NOT NULL,
> mgrid int NULL,
> empname varchar(25) NOT NULL,
> salary money NOT NULL,
> lvl int NOT NULL,
> path varchar(900) NOT NULL
> )
> AS
> BEGIN
> DECLARE @.lvl AS int, @.path AS varchar(900)
> SELECT @.lvl = 0, @.path = '.'
> INSERT INTO @.tree
> SELECT empid, mgrid, empname, salary,
> @.lvl, '.' + CAST(empid AS varchar(10)) + '.'
> FROM Employees
> WHERE empid = @.mgrid
> WHILE @.@.ROWCOUNT > 0
> BEGIN
> SET @.lvl = @.lvl + 1
> INSERT INTO @.tree
> SELECT E.empid, E.mgrid, E.empname, E.salary,
> @.lvl, T.path + CAST(E.empid AS varchar(10)) + '.'
> FROM Employees AS E JOIN @.tree AS T
> ON E.mgrid = T.empid AND T.lvl = @.lvl - 1
> END
> RETURN
> END
> GO
> SELECT REPLICATE (' | ', lvl) + empname AS employee
> FROM ufn_GetSubtree(1)
> ORDER BY path
> /*
> employee
> --
> Nancy
> | Andrew
> | | Steven
> | | Michael
> | Janet
> | | Robert
> | | | David
> | | | | James
> | | | Ron
> | | | Dan
> | | Laura
> | | Ann
> | Margaret
> | | Ina
> */
> -- get leaf nodes of a subtree
> SELECT * FROM ufn_GetSubtree(2) AS S
> WHERE NOT EXISTS(SELECT * FROM Employees AS E
> WHERE E.mgrid = S.empid)
> GO
> -- Get the Chain of Management Leading to a Certain Employee
> CREATE FUNCTION ufn_GetMgmtChain
> (
> @.empid AS int
> )
> RETURNS @.tree table
> (
> empid int NOT NULL,
> mgrid int NULL,
> empname varchar(25) NOT NULL,
> salary money NOT NULL,
> lvl int NOT NULL
> )
> AS
> BEGIN
> DECLARE @.lvl AS int
> SET @.lvl = 0
> INSERT INTO @.tree
> SELECT empid, mgrid, empname, salary, @.lvl
> FROM Employees
> WHERE empid = @.empid
> WHILE @.@.ROWCOUNT > 0
> BEGIN
> SET @.lvl = @.lvl + 1
> INSERT INTO @.tree
> SELECT E.empid, E.mgrid, E.empname, E.salary, @.lvl
> FROM Employees AS E JOIN @.tree AS T
> ON E.empid = T.mgrid AND T.lvl = @.lvl - 1
> END
> RETURN
> END
> GO
> SELECT * FROM ufn_GetMgmtChain(14)
> ORDER BY lvl DESC
> "Ronald Green" <b@.man.com> wrote in message
> news:%23a3iMT1vFHA.2064@.TK2MSFTNGP09.phx.gbl...
>
>
>