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.
pk - ID (Identity)
fk - PID (Foreign key to a master table)
pk - Item_ID (Identity)
fk - ID (Foreign key to [Direct_Construction])