Let us say we have 2 tables that are in a parent-child relation.
We want to make a copy of the FormSectionInstance rows, but also copy and correlate the FormDetails rows.
This means copy rows from 2 tables and then set them up with the correct NEW corresponding IDs.
We could use a cursor, but a better alternative is the the MERGE INTO function.
We also create a TEMP transition table to hold the new VS old values. We use these values when we insert the CHILD values.
This way we can make the INSERT faster.
-- Copy the FormSectionInstance DECLARE @FormSectionInstanceTable TABLE(OldFormSectionInstanceId INT, NewFormSectionInstanceId INT) ;MERGE INTO [dbo].[FormSectionInstance] USING ( SELECT fsi.FormSectionInstanceId [OldFormSectionInstanceId] , @NewFormHeaderId [NewFormHeaderId] , fsi.FormSectionId , fsi.IsClone , @UserId [NewCreatedByUserId] , GETDATE() NewCreatedDate , @UserId [NewUpdatedByUserId] , GETDATE() NewUpdatedDate FROM [dbo].[FormSectionInstance] fsi WHERE fsi.[FormHeaderId] = @FormHeaderId ) tblSource ON 1=0 -- use always false condition WHEN NOT MATCHED THEN INSERT ( [FormHeaderId], FormSectionId, IsClone, CreatedByUserId, CreatedDate, UpdatedByUserId, UpdatedDate) VALUES( [NewFormHeaderId], FormSectionId, IsClone, NewCreatedByUserId, NewCreatedDate, NewUpdatedByUserId, NewUpdatedDate) OUTPUT tblSource.[OldFormSectionInstanceId], INSERTED.FormSectionInstanceId INTO @FormSectionInstanceTable(OldFormSectionInstanceId, NewFormSectionInstanceId); -- Copy the FormDetail INSERT INTO [dbo].[FormDetail] (FormHeaderId, FormFieldId, FormSectionInstanceId, IsOther, Value, CreatedByUserId, CreatedDate, UpdatedByUserId, UpdatedDate) SELECT @NewFormHeaderId, FormFieldId, fsit.NewFormSectionInstanceId, IsOther, Value, @UserId, CreatedDate, @UserId, UpdatedDate FROM [dbo].[FormDetail] fd INNER JOIN @FormSectionInstanceTable fsit ON fsit.OldFormSectionInstanceId = fd.FormSectionInstanceId WHERE [FormHeaderId] = @FormHeaderId