Deep copy of rows + referential integrity? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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)

Deep copy of rows + referential integrity?

Author

Topic

anjhinz
Starting Member

1 Post

Posted - 2007-02-28 : 19:10:57

I have a database of basically this structure:

tblG
---
-GKey (PK)

tblA
---
-AKey (PK)
-GKey (FK -> tblG)
-data

tblB
---
-BKey (PK)
-GKey (FK -> tblG)
-data

tblAB
---
-AKey (FK -> tblA)
-BKey (FK -> tblB)
-data

I'm trying to write a procedure that will take a tblG.GKey and
clone all of its children rows in tblA and tblB (with new PKs),
and then clone all of their children rows in tblAB (using the
new PKs for tblA and tblB).

Does anybody have any suggestions on how this might be done sanely?

Thanks

Kristen
Test

22859 Posts

Posted - 2007-03-02 : 14:13:12

Gather the data into Temporary Table(s), create the tblG row, store the Scope_Identity (or whatever the new PK is), UPDATE the temp table(s) to reference the new PK value, then INSERT ... SELECT ... FROM #TempTable to store the secondary table values ??

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources