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.

where are you copying the data to? Are there no unique constraints other than the primary key? Are there duplicate `(APID,Goal)` and `(APID,GoalID,Objective)`? — SqlZim, Jun 08 '17 at 12:25
Aside: You can use an [`OUTPUT`](https://msdn.microsoft.com/en-us/library/ms177564.aspx) clause to get any data from the rows (Note plural.), e.g. identity column values for newly inserted rows. `OUTPUT` can be used with `INSERT`, `UPDATE`, `DELETE` and `MERGE` and provides access to both _before_ and _after_ values in the case of `UPDATE`. A tool well worth having in your pocket. — HABO, Jun 08 '17 at 17:35

SAS · Accepted Answer · 2017-06-08T14:59:02.780

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

Instead of `@@identity` you should be using `scope_identity()` [What is the difference between Scope_Identity(), Identity(), @@Identity, and Ident_Current?](https://stackoverflow.com/a/1920640/2333499) `@@identity` returns the last identity created in the same session; `scope_identity()` returns the last identity created in the same session and the same scope. — SqlZim, Jun 08 '17 at 14:52

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

1 Answers1