Copying records from the "many" end of a relationship to a new ID on the "one" end

I have a situation where I need to duplicate the records in two tables that have a one to many relationship with eachother.  I can get the items in the parent table to duplicate without any issues by doing the following:

Select * into [TempTable] From [Direct_Construction] where [PID] = @LastPID
Update [TempTable] set [PID] = @NewPID
Insert into [Direct_Construction] Select [Contract_Number], [Firm_Name], [Notes], [Amount], [PID] from [TempTable]
Drop Table [TempTable]

The [Direct_Construction] table has a pk of [id] which is set as an identity column so it auto increments.  The [PID] field is used to tie these two tables to a new project report.

The child table contains the line items for the parent which is called [Direct_Construction_Items].  It uses the pk of [Direct_Construction] as a foreign key.  If I were to do a copy the same way as I did above the new duplicated records still point to the ID of the old record in [Direct_Construction].  How can I duplicate these records and update the fk - [ID] field to match that of its duplicated parent records new [ID]?

I'd like to accomplish this all within a stored procedure for this db if possible.

Tables
[Direct_Construction]
pk - ID (Identity)
Contract_Number
Firm_Name
Notes
Amount
fk - PID (Foreign key to a master table)

[Direct_Construction_Items]
pk - Item_ID  (Identity)
Amount
Item
Notes
fk - ID (Foreign key to [Direct_Construction])

Thanks!
WSU-CPDAsked:
Who is Participating?
 
JoeNuvoConnect With a Mentor Commented:
my above comment is wrong. Please ignore it.

here is the correct one.

-- variable to keep data
Declare @OldPkID int
Declare @NewPkID int

-- get the old PK to refer in  [Direct_Construction_Items] table
SELECT @OldPkID = ID FROM [Direct_Construction] WHERE [PID] = @LastPID 

-- your code
Select * into [TempTable] From [Direct_Construction] where [PID] = @LastPID
Update [TempTable] set [PID] = @NewPID
Insert into [Direct_Construction] Select [Contract_Number], [Firm_Name], [Notes], [Amount], [PID] from [TempTable]

-- get new PK value
SELECT @NewPkID = SCOPE_IDENTITY()

-- insert data into table with new Pk
INSERT INTO [Direct_Construction_Items] (Amount, Item, Notes, ID)
SELECT Amount, Item, Notes, @NewPkID
FROM [Direct_Construction_Items]
WHERE ID = @OldPkID

-- your code
Drop Table [TempTable]

Open in new window

0
 
JoeNuvoCommented:
add insert command as following

Select * into [TempTable] From [Direct_Construction] where [PID] = @LastPID
Update [TempTable] set [PID] = @NewPID
Insert into [Direct_Construction] Select [Contract_Number], [Firm_Name], [Notes], [Amount], [PID] from [TempTable]

INSERT INTO [Direct_Construction_Items] (Amount, Item, Notes, ID)
SELECT Amount, Item, Notes, @NewPID
FROM [Direct_Construction_Items]
WHERE ID = @LastPID

Drop Table [TempTable]

Open in new window

0
 
WSU-CPDAuthor Commented:
JoeNuvo,

First off let me say thank you thank you thankyou!  This works perfectly!  However, if I have a situation where there are two parent records that each have two records in the child table.  Only the second record in the parent tables child items get updated.  Is there a way to do some sort of while loop to cycle through each record of the parent table and apply your code.

0
 
WSU-CPDAuthor Commented:
JoeNuvo,

Using your code I added a while loop and am successfully getting each item in the parent table but it only updates the first child item for each parent item.  Here is what I have so far.  Anyone have any ideas?
-- get the old PK to refer in  [Direct_Construction_Items] table
SELECT @OldPkID = ID FROM [Direct_Construction] WHERE [PID] = @LastPID 

CREATE TABLE [TempTable] (ID int, Contract_Number nvarchar(50), Firm_Name nchar(255), Notes nchar(255), Amount MONEY, PID int, RowID int IDENTITY)

-- your code
INSERT INTO [TempTable] SELECT * From [Direct_Construction] where [PID] = @LastPID

SET @maxrows = @@rowcount
Set @rowid = 1

Update [TempTable] set [PID] = @NewPID

While @rowid <= @maxrows

BEGIN

Insert into [Direct_Construction] Select [Contract_Number], [Firm_Name], [Notes], [Amount], [PID] from [TempTable] where [rowid]=@rowid

-- get new PK value
SELECT @NewPkID = SCOPE_IDENTITY()

-- insert data into table with new Pk
INSERT INTO [Direct_Construction_Items] (Amount, Item, Notes, ID, PID)
SELECT Amount, Item, Notes, @NewPkID, @NewPID
FROM [Direct_Construction_Items] 
WHERE ID = @OldPkID

SET @rowid=@rowid + 1

END

-- your code
Drop Table [TempTable]

Open in new window

0
 
WSU-CPDAuthor Commented:
Got it figured out!  Thank you Joe!  I used your code for the Scope_identity function and added a while loop to cycle through each record and update accordingly.  You get all the credit though!
-- variable to keep data
Declare @OldPkID int
Declare @NewPkID int
Declare @rowid int
Declare @maxrows in

CREATE TABLE [TempTable] (ID int, Contract_Number nvarchar(50), Firm_Name nchar(255), Notes nchar(255), Amount MONEY, PID int, RowID int IDENTITY)

INSERT INTO [TempTable] SELECT * From [Direct_Construction] where [PID] = @LastPID

SET @maxrows = @@rowcount
Set @rowid = 1

Update [TempTable] set [PID] = @NewPID

While @rowid <= @maxrows

BEGIN

-- get the old PK to refer in  [Direct_Construction_Items] table
SELECT @OldPkID = ID FROM [TempTable] WHERE [RowID]=@rowid

Insert into [Direct_Construction] Select [Contract_Number], [Firm_Name], [Notes], [Amount], [PID] from [TempTable] where [RowID]=@rowid

-- get new PK value
SELECT @NewPkID = SCOPE_IDENTITY()

Select * into [ItemTempTable] From [Direct_Construction_Items] where [ID] = @OldPkID AND [PID] = @LastPID 
Update [ItemTempTable] set [PID] = @NewPID, [ID] = @NewPkID
Insert into [Direct_Construction_Items] Select [Amount], [Item], [Notes], [ID], [PID] from [ItemTempTable]
Drop Table [ItemTempTable]

SET @rowid=@rowid + 1

END

Drop Table [TempTable]

Open in new window

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.