Solved

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

Posted on 2011-03-02
5
161 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
5 Comments
 
LVL 11

Expert Comment

by:JoeNuvo
ID: 35023564
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
ID: 35023591
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
ID: 35028757
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
ID: 35029338
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
ID: 35030302
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

Edgartown IT Case Study

Learn about Edgartown's quest to ensure the safety and security of the entire town's employee and citizen data. Read the case study!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

737 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