How to get id's of parent ids for inserting children
<p>I have Parent and Child table.  </p>

<p>The goal is to duplicate the records, except with new primary keys.</p>

<p>Original Tables</p>

<pre><code>Parent(id)
1

Child(id,parentId, data)
1,1
2,1
</code></pre>

<p>After insert:</p>

<pre><code>Parent
1
2

Child
1,1
2,1
3,2
4,2
</code></pre>

<p>How do I do that? The part I am having trouble with is getting the new parent key for use with the child records.</p>

<p>This is what I have come up with so far.</p>

<pre><code>--DECLARE VARS
declare @currentMetadataDocumentSetId int = 1, --Ohio
    @newMetadataDocumentSetid int = 3; --PA

--CLEANUP
IF OBJECT_ID('tempdb..#tempFileRowMap') IS NOT NULL
  /*Then it exists*/
  DROP TABLE #tempFileRowMap

--Remove existing file row maps.
delete from file_row_map where metadata_document_set_id = @newMetadataDocumentSetid;


--Create a temptable to hold data to be copied.
Select [edi_document_code], 
    [functional_group], 
    [description], 
    3 as [metadata_document_set_id], 
    [document_name], 
    [incoming_file_row_subtype], 
    [metadata_document_id], 
    [document_subcode], 
    [outgoing_file_row_subtype], 
    [asi_type_code], 
    [asi_action_code], 
    [metadata_document_set],
    file_row_map_id as orig_file_row_map_id 
into #tempFileRowMap
from file_row_map fileRowMap
where metadata_document_set_id = @currentMetadataDocumentSetId;


--Select * from #tempFileRowMap;
Insert into file_row_map select
[edi_document_code], 
[functional_group], 
[description], 
[metadata_document_set_id], 
[document_name], 
[incoming_file_row_subtype], 
[metadata_document_id], 
[document_subcode], 
[outgoing_file_row_subtype], 
[asi_type_code], 
[asi_action_code], 
[metadata_document_set]
from #tempFileRowMap

--Show Results
Select * from file_row_map fileRowMap where fileRowMap.metadata_document_set_id = @newMetadataDocumentSetid


--Update Detail
Select
[file_row_map_id], 
[file_row_column], 
[element_code], 
[element_metadata_id], 
[col_description], 
[example], 
[translate], 
[is_used], 
[is_mapped], 
[page_num], 
[subcode], 
[qualifier], 
[loop_code], 
[loop_subcode], 
[default_value], 
[delete_flag]
into #tempFileRowMapDetail
from [dbo].[file_row_map_detail] d
left join #tempFileRowMap m
on m.orig_file_row_map_id = d.file_row_map_id

select * from #tempFileRowMapDetail
</code></pre>