Copying Parent-Child with Identity foreign key

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Copying Parent-Child with Identity foreign key

Author

Topic

ocajuno
Starting Member

5 Posts

Posted - 2008-08-07 : 12:22:59

I have a parent table with an identity primary key, and a child table with an identity primary key with a foreign key to the parent identity.

I'd like to make a clone of some of the parents and their children. Is there a way to accomplish this without cursors?

Thanks, Randy

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-07 : 12:41:29

By clone do you mean inserting some new parent rows along with new child rows wit generated ids?

ocajuno
Starting Member

5 Posts

Posted - 2008-08-07 : 13:01:21

Yes.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-07 : 13:10:14

ok try like this

DECLARE @INSERTED_IDS table
(ID int,
Other columns from parent)

INSERT INTO Parent (otherfields)
OUTPUT INSERTED.ID,INSERTED.otherfields INTO @INSERTED_IDS
SELECT fields
FROM Parent
WHERE condition

INSERT INTO Child (fields,parentfk)
SELECT fieldvalues,i.ID
FROM Child c
JOIN Parent p
ON p.ID=c.Parentfk
JOIN @INSERTED_IDS i
ON i.otherfields=p.correspondingfields

if you want more detailed soln then post your table structures.

ocajuno
Starting Member

5 Posts

Posted - 2008-08-07 : 14:07:59

Thanks for the reply, but I can't use the SQL2005 OUTPUT clause because we must support SQL2000.

Just to clarify the original problem:

Table1 contains Table1ID (identity) and Table2Id (FK to Table2) columns.
Table2 contains Table2ID (identity).

I'd like to make a copy of the Table1 and Table2 records without using a cursor.

Also, can a temp table be avoided?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-07 : 14:15:08

I gave you sql 2005 solution as you had posted in sql 2005 forum.
In sql 2000 you can do this by means of a trigger.

ocajuno
Starting Member

5 Posts

Posted - 2008-08-07 : 14:34:03

Thanks for the suggestion. Any other approaches out there?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-07 : 14:46:38

quote:
Originally posted by ocajuno

Thanks for the suggestion. Any other approaches out there?

try this too

INSERT INTO Parent (otherfields)
SELECT fields
FROM Parent
WHERE condition

INSERT INTO Child (fields,parentfk)
SELECT fieldvalues,p2.ID
FROM Child c
JOIN Parent p
ON p.ID=c.Parentfk
JOIN (SELECT MAX(ID),otherfieds
      FROM Parent 
      GROUP BY otherfieds)p2
ON p.otherfields=p2.correspondingfields

alirizvi9
Starting Member

1 Post

Posted - 2009-06-23 : 17:21:04

Thank you, thank you, thank you... works GREAT!!!

quote:

Originally posted by visakh16

ok try like this

DECLARE @INSERTED_IDS table
(ID int,
Other columns from parent)

INSERT INTO Parent (otherfields)
OUTPUT INSERTED.ID,INSERTED.otherfields INTO @INSERTED_IDS
SELECT fields
FROM Parent
WHERE condition

INSERT INTO Child (fields,parentfk)
SELECT fieldvalues,i.ID
FROM Child c
JOIN Parent p
ON p.ID=c.Parentfk
JOIN @INSERTED_IDS i
ON i.otherfields=p.correspondingfields

if you want more detailed soln then post your table structures.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources