Solved

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

Posted on 2011-03-02
5
159 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
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

830 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