Deep copy of child rows + referential integrity?

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?

ThanksWhat are the primary keys? Are they identity columns? How are they generated? Also, what version of SQL Server are you using?|||I'm in SQL Server 2005.

The primary keys (marked PK) are identity columns and are generated automatically.
Using "select IDENT_CURRENT('tblA')" works as I would expect it to.

I feel like I need to traverse all the rows in tblA and tblB that need to be copied, insert them, then grab the IDENT_CURRENT off that row and insert into a temporary table along with the original. Then go through all the rows in tblAB and replace the FKs with look-ups from the temporary table. I just have no idea how I could implement that.|||

In my mind it's still not totally clear what you're trying to do.

Is there a chance you could post a short example (around 4 rows from each table) of the data you'd expect to see in your tables before and after the changes have been made?

Thanks
Chris

|||Sure. Here are the tables before the copy:

"tblG"
-
GKey |
-
1

"tblA"
-
AKey | GKey | data
1 | 1 | abc
2 | 1 | def
3 | 1 | ghi

"tblB"

-

BKey | GKey | data

1 | 1 | jkl

2 | 1 | mno

3 | 1 | oqr

"tblAB"

-

AKey | BKey | data

1 | 3 | tuv

1 | 2 | xyz

3 | 1 | aaa
2 | 1 | bbb

And now after the copy:

"tblG"

-

GKey |

-

1

2

"tblA"

-

AKey | GKey | data

1 | 1 | abc

2 | 1 | def

3 | 1 | ghi

4 | 2 | abc

5 | 2 | def

6 | 2 | ghi

"tblB"

-

BKey | GKey | data

1 | 1 | jkl

2 | 1 | mno

3 | 1 | oqr

4 | 2 | jkl

5 | 2 | mno

6 | 2 | oqr

"tblAB"

-

AKey | BKey | data

1 | 3 | tuv

1 | 2 | xyz

3 | 1 | aaa

2 | 1 | bbb

4 | 6 | tuv

4 | 5 | xyz

6 | 4 | aaa

5 | 4 | bbb

Does that make sense?|||

Although I generally favour set-based approaches, I can't think of set-based method that wouldn't require a modification to your existing tables. The cursor-based example below returns the results as you stipulated in your previous post.

Essentially, one row at a time is inserted into tblA and the old and new IDENTITY values stored away in a table variable. The same is done for tblB. It's then a simple matter of joining the two table variables onto tblAB to copy the relationships that exist in tblAB.

If you desperately need a set-based method, and can make changes to your tables, then you could add an extra column to both tblA and tblB to store the ID of the row from which the current row was copied. As long as this column is maintained during subsequent INSERTs then you can simply modify and use the final INSERT statement of the code I've included below to populate tblAB.

Chris

/*

CREATE TABLE dbo.tblG

(

GKey INT

)

CREATE TABLE dbo.tblA

(

AKey INT IDENTITY(1, 1) PRIMARY KEY,

GKey INT,

Data VARCHAR(3)

)

CREATE TABLE dbo.tblB

(

BKey INT IDENTITY(1, 1) PRIMARY KEY,

GKey INT,

Data VARCHAR(3)

)

CREATE TABLE dbo.tblAB

(

AKey INT,

BKey INT,

Data VARCHAR(3)

)

INSERT INTO dbo.tblG

VALUES(1)

SET IDENTITY_INSERT dbo.tblA ON

INSERT INTO dbo.tblA(AKey, GKey, Data)

SELECT 1, 1, 'abc' UNION

SELECT 2, 1, 'def' UNION

SELECT 3, 1, 'ghi'

SET IDENTITY_INSERT dbo.tblA OFF

SET IDENTITY_INSERT dbo.tblB ON

INSERT INTO dbo.tblB(BKey, GKey, Data)

SELECT 1, 1, 'jkl' UNION

SELECT 2, 1, 'mno' UNION

SELECT 3, 1, 'pqr'

SET IDENTITY_INSERT dbo.tblB OFF

INSERT INTO dbo.tblAB(AKey, BKey, Data)

SELECT 1, 3, 'tuv' UNION

SELECT 1, 2, 'xyz' UNION

SELECT 3, 1, 'aaa' UNION

SELECT 2, 1, 'bbb'

*/

DECLARE @.OldGKey INT

DECLARE @.NewGKey INT

DECLARE @.OldAKey INT

DECLARE @.NewAKey INT

DECLARE @.OldBKey INT

DECLARE @.NewBKey INT

DECLARE @.AKeyNewOld TABLE (OldAKey INT, NewAKey INT)

DECLARE @.BKeyNewOld TABLE (OldBKey INT, NewBKey INT)

DECLARE @.Data VARCHAR(3)

SELECT @.OldGKey = MAX(GKey), @.NewGKey = MAX(GKey + 1)

FROM dbo.tblG

INSERT INTO dbo.tblG

VALUES(@.NewGKey)

DECLARE curAKeys CURSOR FAST_FORWARD LOCAL FOR

SELECT AKey, Data

FROM dbo.tblA

WHERE GKey = @.OldGKey

ORDER BY AKey

OPEN curAKeys

FETCH NEXT FROM curAKeys INTO @.OldAKey, @.Data

WHILE @.@.FETCH_STATUS = 0

BEGIN

INSERT INTO tblA(GKey, Data)

VALUES(@.NewGKey, @.Data)

SET @.NewAKey = SCOPE_IDENTITY()

INSERT INTO @.AKeyNewOld(OldAKey, NewAKey)

VALUES(@.OldAKey, @.NewAKey)

FETCH NEXT FROM curAKeys INTO @.OldAKey, @.Data

END

CLOSE curAKeys

DEALLOCATE curAKeys

DECLARE curBKeys CURSOR FAST_FORWARD LOCAL FOR

SELECT BKey, Data

FROM dbo.tblB

WHERE GKey = @.OldGKey

ORDER BY BKey

OPEN curBKeys

FETCH NEXT FROM curBKeys INTO @.OldBKey, @.Data

WHILE @.@.FETCH_STATUS = 0

BEGIN

INSERT INTO tblB(GKey, Data)

VALUES(@.NewGKey, @.Data)

SET @.NewBKey = SCOPE_IDENTITY()

INSERT INTO @.BKeyNewOld(OldBKey, NewBKey)

VALUES(@.OldBKey, @.NewBKey)

FETCH NEXT FROM curBKeys INTO @.OldBKey, @.Data

END

CLOSE curBKeys

DEALLOCATE curBKeys

INSERT INTO dbo.tblAB(AKey, BKey, Data)

SELECT akno.NewAKey,

bkno.NewBKey,

tab.Data

FROM dbo.tblAB tab

INNER JOIN @.AKeyNewOld akno ON akno.OldAKey = tab.AKey

INNER JOIN @.BKeyNewOld bkno ON bkno.OldBKey = tab.BKey

SELECT * FROM tbLG

SELECT * FROM tbLA

SELECT * FROM tbLB

SELECT * from tblAB

|||Works like a charm. Thanks a million