How to get id's of parent ids for inserting children

Question

I have Parent and Child table.

The goal is to duplicate the records, except with new primary keys.

Original Tables

Parent(id)
1

Child(id,parentId, data)
1,1
2,1

After insert:

Parent
1
2

Child
1,1
2,1
3,2
4,2

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

This is what I have come up with so far.

--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

[This might help](http://stackoverflow.com/a/38217498/3094533) — Zohar Peled, Dec 26 '16 at 20:17
@ZoharPeled, Interesting link on the merge. Didn't know about 'merge' before. — Greg Gum, Dec 30 '16 at 19:08

Shakeer Mirza · Accepted Answer · 2016-12-27T05:46:10.073

Simply use OUTPUT clause for getting exact Parent Table Primary Key values.

Lets build Example Schema for your case

--For Capturing inserted ID
CREATE TABLE #ID_CAPTURE  (PARENT_ID INT,ORDER_NME VARCHAR(20));

--Your Intermidiate Data To insert into Actual Tables
CREATE TABLE #DUMMY_TABLE (ORDER_NME VARCHAR(20), ITEM_NME VARCHAR(20));

--Actual Tables
CREATE TABLE  #ORDER_PARENT (ORDER_ID INT IDENTITY,ORDER_NME VARCHAR(20))
CREATE TABLE  #ORDER_CHILD (CHILD_ID INT IDENTITY ,ORDER_ID INT, ORDER_NME VARCHAR(20))


INSERT INTO #DUMMY_TABLE
SELECT 'BILL1','Oil'
UNION ALL
SELECT 'BILL1', 'Gas'
UNION ALL
SELECT 'BILL2', 'Diesel'

Now do Inserts in Parent & Child Tables

INSERT INTO #ORDER_PARENT
OUTPUT inserted.ORDER_ID, inserted.ORDER_NME into #ID_CAPTURE

SELECT DISTINCT ORDER_NME FROM #DUMMY_TABLE


INSERT INTO #ORDER_CHILD

SELECT C.PARENT_ID, ITEM_NME FROM #DUMMY_TABLE D
INNER JOIN #ID_CAPTURE C ON D.ORDER_NME = C.ORDER_NME



SELECT * FROM #ID_CAPTURE
SELECT * FROM #ORDER_CHILD

There are other ways to get Inserted Identity values.

See documentation @@IDENTITY (Transact-SQL) , SCOPE_IDENTITY

score 0 · Answer 2 · answered Dec 26 '16 at 21:26

Try following approach:

DECLARE @Table1 TABLE (
    ID          INT NOT NULL PRIMARY KEY,
    ParentID    INT NULL, -- FK
    [Desc]      VARCHAR(50) NOT NULL
);
INSERT  @Table1 (ID, ParentID, [Desc])
VALUES  
(1, NULL, 'A'),
(2, 1, 'AA.1'),
(3, 1, 'AA.2'),
(4, NULL, 'B'),
(5, 4, 'BB.1'),
(6, 4, 'BB.2'),
(7, 4, 'BB.3'),
(8, 7, 'BBB.1');

DECLARE @ParentID INT = 4;

DECLARE @LastID INT = (SELECT TOP(1) ID FROM @Table1 x ORDER BY x.ID DESC)
IF @LastID IS NULL
BEGIN
    RAISERROR('Invalid call', 16, 1)
    --RETURN ?
END
SELECT @LastID AS LastID;
/*
LastID
-----------
8
*/

DECLARE @RemapIDs TABLE (
    OldID   INT NOT NULL PRIMARY KEY,
    [NewID] INT NOT NULL UNIQUE
);

WITH CteRecursion
AS (
    SELECT  1 AS Lvl, crt.ID, crt.ParentID --, crt.[Desc]
    FROM    @Table1 crt
    WHERE   crt.ID = @ParentID
    UNION ALL 
    SELECT  cld.Lvl + 1 AS Lvl, crt.ID, crt.ParentID --, crt.[Desc]
    FROM    @Table1 crt
    JOIN    CteRecursion cld ON crt.ParentID = cld.ID
)
INSERT  @RemapIDs (OldID, [NewID])
SELECT  r.ID, @LastID + ROW_NUMBER() OVER(ORDER BY r.Lvl) AS [NewID]
FROM    CteRecursion r;

--INSERT    @Table1 (ID, ParentID, [Desc])
SELECT  nc.[NewID] AS ID, np.[NewID] AS ParentID, o.[Desc]
FROM    @Table1 o -- old
JOIN    @RemapIDs nc /*new child ID*/ ON o.ID = nc.OldID
LEFT JOIN @RemapIDs np /*new parent ID*/ ON o.ParentID = np.OldID
/*
ID          ParentID    Desc
----------- ----------- --------------------------------------------------
9           NULL        B
10          9           BB.1
11          9           BB.2
12          9           BB.3
13          12          BBB.1
*/

Note: with some minor changes should work w. many ParentIDs values.

Could you add a bit of explanation? I am sure this is a good answer, but my skills are not up to understanding it just from SQL. — Greg Gum, Dec 30 '16 at 19:07

How to get id's of parent ids for inserting children

2 Answers2