Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 167
  • Last Modified:

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!
0
WSU-CPD
Asked:
WSU-CPD
  • 3
  • 2
1 Solution
 
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
 
JoeNuvoCommented:
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
 
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

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now