Insert multiple parent-child records, Sql Server

Insert multiple parent-child records

-- Table setup

CREATE TABLE Parent
(
    Parent_Id int IDENTITY(1,1) CONSTRAINT PK_Parent PRIMARY KEY,
    Parent_Name varchar(10) NOT NULL CONSTRAINT UC_Parent_Name UNIQUE
);

CREATE TABLE Child
(
    Child_Id int IDENTITY(1,1) CONSTRAINT PK_Child PRIMARY KEY,
    Child_Name varchar(10) NOT NULL CONSTRAINT UC_ChildName UNIQUE,
    Child_ParentId int CONSTRAINT FK_Child_Parent REFERENCES Parent(Parent_Id)
);

-- User defined table types setup

CREATE TYPE udt_Parent AS TABLE
(
    Parent_Id int,
    Parent_Name varchar(10)
);

CREATE TYPE udt_Child AS TABLE
(
    Child_ParentId int,
    Child_Name varchar(10)
);

-- The procedure

CREATE PROCEDURE InsertMultipleParentsAndChildren
(
    @Parents dbo.udt_Parent readonly,
    @Children dbo.udt_Child readonly
)
AS

DECLARE @Map AS TABLE
    (
        TempId int,
        InsertedId int
    )

MERGE INTO Parent USING @Parents AS temp ON 1 = 0 -- Always not matched
    WHEN NOT MATCHED THEN
        INSERT (Parent_Name)
        VALUES (temp.Parent_Name)
        OUTPUT temp.Parent_Id, inserted.Parent_Id 
        INTO @idMap (TempId, InsertedId);

INSERT INTO Child(Child_Name, Child_ParentId)
    SELECT Child_Name, InsertedId
    FROM @Children as c
    INNER JOIN @Map as m ON(r.Child_ParentId = m.TempId)


run \| edit \| history \| help	0