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

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 Rosi
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
-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
>

Copy/move data from TableA in DB1 to TableB in DB2 within the same SQL Server

Hallo,
I thouhgt it would be easily done, but no. I have a copy of data i saved in
another DB while laying out the original Database i want to use in producto
n Envi. The Prodution DB is finally ready and i want to move the 3 columns
of data in the first Databs
e into a table in the new database. I have tried "select into", "copy" with
out success.
The old DBTable has 3 Columns of hundreds of rows that i must move to 3 simi
lar columns in a table (15 columns) in the new database. The old table does
not have an ID, but the new table does.
Can someone help. I want to show this to my boss tomorrow.
Thanks.How would you relate the rows in the two tables to each other?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Omooba" <anonymous@.discussions.microsoft.com> wrote in message
news:B2D46104-7C63-4907-AB44-F8EC6FF860A4@.microsoft.com...
> Hallo,
> I thouhgt it would be easily done, but no. I have a copy of data i saved
in another DB while laying out the original Database i want to use in
producton Envi. The Prodution DB is finally ready and i want to move the 3
columns of data in the first Databse into a table in the new database. I
have tried "select into", "copy" without success.
> The old DBTable has 3 Columns of hundreds of rows that i must move to 3
similar columns in a table (15 columns) in the new database. The old table
does not have an ID, but the new table does.
> Can someone help. I want to show this to my boss tomorrow.
> Thanks.