Insert multiple parent-child records, Sql Server

Insert multiple parent-child records

/************************************************************

This is a demonstration on how to insert multiple parent records
and multiple child records in the same stored procedure.

First version published on stackoverflow and contains an explanation:

https://stackoverflow.com/questions/34826450/fastest-way-to-perform-nested-bulk-inserts-with-scope-identity-usage

Second version with some more explanations can be found on this stackoverflow answer:

https://stackoverflow.com/a/38217498/3094533

************************************************************/

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

-- Using merge instead of a regular insert statement allows us to use both sorce and target values in the output clause
    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 @Map (TempId, InsertedId);

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


run \| edit \| history \| help	0