Copying rows with all child rows

Hallo NG,
I have some complicated database with many relational tables and one root
table (i.e. has no FK)
What I need to do is to copy a row inside the root table then copy all child
rows and change references in them to refer to the copied row in the root
table, and then copy the child rows from child rows and make them referring
to the copied root child row . and so on until the last level.
I thought about making a strong typed DataSet for this database and then
loading the relevant rows, call MemberwiseClone on it and then update the
DataSet, anyway this way is very inefficient because I need to retrieve all
data from the sql server to the computer running .Net and then sending the
copied data back.
Does anybody know a way to do this job using just T-SQL ?
Thanks in advance
Stefan RosiI'm not sure of the details of what you're asking. but if I understand
correctly this is the direction I'd try...
INSERT RootCopy
FROM Root
WHERE Rootexpression
INSERT ChildCopy
FROM Child
JOIN Root
ON Root.PK = Child.FK
WHERE Rootexpression
-Paul Nielsen, SQL Server MVP
SQL Server 2000 Bible, Wiley Press
Enterprise Data Architect, www.Compassion.com
"Stefan Rosi" <Stefan.Rosie_ReplaceWithAt_gmail.com> wrote in message
news:OxBkPic0EHA.824@.TK2MSFTNGP11.phx.gbl...
>
> Hallo NG,
>
> I have some complicated database with many relational tables and one root
> table (i.e. has no FK)
> What I need to do is to copy a row inside the root table then copy all
> child
> rows and change references in them to refer to the copied row in the root
> table, and then copy the child rows from child rows and make them
> referring
> to the copied root child row . and so on until the last level.
> I thought about making a strong typed DataSet for this database and then
> loading the relevant rows, call MemberwiseClone on it and then update the
> DataSet, anyway this way is very inefficient because I need to retrieve
> all
> data from the sql server to the computer running .Net and then sending the
> copied data back.
> Does anybody know a way to do this job using just T-SQL ?
>
> Thanks in advance
> Stefan Rosi
>sql