Copying rows with all child rows

[Stefan Rosi]

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

 

[Paul Nielsen]

I'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