tsql - How to copy data in the same table with new ID and with foreign key relationship

Question

I have 3 tables, All 3 tables have Auto-Increment primary key column with foreign key relationship.

My problem is that I want to copy the related data in same table.

And also to maintain the PK-->FK relationship.

For example I want to copy GoalID = 1. I have also created variable tables and data as my scenario.

Declare @tblCompanyGoal Table
(
GoalID int identity(1,1), --PKID
APID int,
Goal nvarchar(500)
)

Declare @tblPMCompanyObjectives Table
(
ObjectID int identity(1,1), --PKID
ApID int,   
Objective   nvarchar(500),
GoalID int --FK --> @tblCompanyGoal.GoalID
)

Declare @tblPMCompanyStrategies Table
(
StrgID int identity(1,1), --PKID
Strategies nvarchar(500),
ObjectID int --FK --> @@tblPMCompanyObjectives.ObjectID
)


Insert into @tblCompanyGoal (APID, Goal)Values(500, 'C-Goal1')
Insert into @tblCompanyGoal (APID, Goal)Values(600, 'C-Goal2')

insert into @tblPMCompanyObjectives (ApID, Objective, GoalID)values(500, 'BF', 1)
insert into @tblPMCompanyObjectives (ApID, Objective, GoalID)values(500, 'LF', 1)
insert into @tblPMCompanyObjectives (ApID, Objective, GoalID)values(500, 'BFA', 2)

Insert into @tblPMCompanyStrategies(Strategies, ObjectID)Values('GTK', 1)
Insert into @tblPMCompanyStrategies(Strategies, ObjectID)Values('GTK2', 1)
Insert into @tblPMCompanyStrategies(Strategies, ObjectID)Values('ASK', 1)
Insert into @tblPMCompanyStrategies(Strategies, ObjectID)Values('WER', 2)
Insert into @tblPMCompanyStrategies(Strategies, ObjectID)Values('NFT', 2)
Insert into @tblPMCompanyStrategies(Strategies, ObjectID)Values('KRG', 3)

How can I accomplish this task? I have tried it with cursor but not succeeded.

Answer 1

After the insert into @tblCompanyGoal Table, use the SCOPE_IDENTITY value. It will automatically contain the new identity ID (for the newly inserted row) from the last insert statement. (You could also make a join like in the last insert, if you prefer without a variable.)

Then use this value in the following insert, as the new foreign key value.

The last join is a little bit tricky, you have to map the old ids with the new ones. I added some comments to help explain the logic.

This will give you a copy of the related data.

  DECLARE @NewGoalID INT, @OldGoalID INT

  SET @OldGoalID = 1

  INSERT INTO @tblCompanyGoal(APID, Goal) 
  SELECT APID, Goal FROM @tblCompanyGoal WHERE GoalID=1

  SET @NewGoalID = SCOPE_IDENTITY()

  INSERT INTO @tblPMCompanyObjectives(ApID, Objective, GoalID) 
  SELECT ApID, Objective, @NewGoalID FROM @tblPMCompanyObjectives WHERE GoalID = @OldGoalID

  INSERT INTO @tblPMCompanyStrategies(Strategies, ObjectID) 
  SELECT s.Strategies, no.ObjectID
  -- get the old object rows
  FROM @tblPMCompanyStrategies s
  INNER JOIN @tblPMCompanyObjectives oo ON oo.objectid = s.objectid
  INNER JOIN @tblCompanyGoal oc ON oc.GoalID = oo.GoalID AND oc.GoalID = @OldGoalID
  -- get the matching rows new object IDs
  INNER JOIN @tblPMCompanyObjectives no ON no.GoalID = @NewGoalID AND no.ApID=oo.ApID AND no.Objective=oo.Objective