Solved

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

Posted on 2011-03-02
5
152 Views
Last Modified: 2012-05-11
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
Comment
Question by:WSU-CPD
  • 3
  • 2
5 Comments
 
LVL 11

Expert Comment

by:JoeNuvo
Comment Utility
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
 
LVL 11

Accepted Solution

by:
JoeNuvo earned 500 total points
Comment Utility
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
 

Author Comment

by:WSU-CPD
Comment Utility
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
 

Author Comment

by:WSU-CPD
Comment Utility
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
 

Author Comment

by:WSU-CPD
Comment Utility
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

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

Suggested Solutions

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now