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
Kevin.McPhail wrote: