SQL Server 2019

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
/*

This is a demonstration for a post on What The # Do I Know?

Post URL: 

https://zoharpeled.wordpress.com/2020/05/17/insert-multiple-parents-with-multiple-children-in-a-single-stored-procedure/

*/
/**********************************************************************
 *    NOTE: TblCusromers and TblItems are needed for this demo        *
 *    only as targets of foreign key constraints.                     *
 **********************************************************************/

CREATE TABLE dbo.TblCustomers
(
    Id int NOT NULL Identity(1,1) CONSTRAINT PK_Customers PRIMARY KEY,
    FirstName varchar(10),
    LastName varchar(10),
    -- Other related columns such as email, (password (hashed and salted!) etc'
);

CREATE TABLE dbo.TblItems 
(
    Id int NOT NULL Identity(1,1) CONSTRAINT PK_Items PRIMARY KEY,
    Name char(1) -- Naturally, this will be more than one char...
);
/*************************************************************
 *     Relevent tables are TblOrders and TblOrdersDetails    *
 *************************************************************/

CREATE TABLE dbo.TblOrders
(
    Id int NOT NULL identity(1,1) 
        CONSTRAINT PK_Orders PRIMARY KEY,
    CustomerId int 
        CONSTRAINT FK_Orders_Customers 
        FOREIGN KEY REFERENCES dbo.TblCustomers(Id),
    CreateDate DateTime2 NOT NULL 
        CONSTRAINT DF_Orders_CreateDate 
        DEFAULT(SYSDATETIME()),
    -- Other order related columns such as status, delivery date etc'    
);


CREATE TABLE dbo.TblOrderDetails
(
    Id int NOT NULL Identity(1,1)
        CONSTRAINT PK_OrderDetails PRIMARY KEY,
    OrderId int
        CONSTRAINT FK_OrderDetails_Orders 
        FOREIGN KEY REFERENCES dbo.TblOrders(Id),
    ItemId int
        CONSTRAINT FK_OrderDetails_Items 
        FOREIGN KEY REFERENCES dbo.TblItems(Id),
    Quantity int, -- assumes all itema can only be auqntified using ints...
    -- other related columns such as price per unit (at the time of the order) etc'
);
CREATE TYPE dbo.udtOrders AS TABLE
(
    Id int,
    CustomerId int, 
    CreateDate DateTime2    
    -- Other order related columns such as:
    -- status, delivery date etc'    
);

CREATE TYPE dbo.udtOrderDetails AS TABLE
(
    OrderId int,
    ItemId int, 
    Quantity int 
    -- other related columns such as price per unit 
    -- (at the time of the order) etc'
);
CREATE PROCEDURE InsertMultipleOrders
(
    @Orders dbo.udtOrders readonly,
    @OrderDetails dbo.udtOrderDetails readonly
)
AS

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

BEGIN TRANSACTION

BEGIN TRY

    MERGE INTO dbo.TblOrders USING @Orders AS source 
        ON 1 = 0 -- Always not matched
    WHEN NOT MATCHED THEN
        INSERT (CustomerId
              , CreateDate 
           -- , other related columns
        )
        VALUES (source.CustomerId
              , source.CreateDate 
           -- , other related columns
        )
        OUTPUT source.Id, inserted.Id 
        INTO @Map (TempId, InsertedId);

    INSERT INTO dbo.TblOrderDetails(
         OrderId
       , ItemId
       , Quantity
    -- , other related columns
    )
    SELECT InsertedId
         , ItemId
         , Quantity
      -- , other related columns
    FROM @OrderDetails as OD
    INNER JOIN @Map as Map 
        ON(OD.OrderId = Map.TempId);

    COMMIT TRANSACTION;
END TRY
BEGIN CATCH
    IF @@TRANCOUNT > 0
        ROLLBACK TRANSACTION;
END CATCH
-- We're going to need some data in the customers and items tables first...
INSERT INTO dbo.TblCustomers (FirstName, LastName) 
VALUES ('Zohar', 'Peled');

INSERT INTO dbo.TblItems (Name) VALUES
('A'), ('B'), ('C'), ('D'), ('E'), ('F'), ('G'), ('H'), ('I');
10 rows affected

-- Now - the demo:

DECLARE @Orders dbo.udtOrders;
        
INSERT INTO @Orders (Id, CustomerId, CreateDate) 
VALUES (10, 1, '2020-05-17T17:00:00'),
       (20, 1, '2020-05-17T17:30:00'),
       (30, 1, '2020-05-17T18:00:00');


DECLARE @OrderDetails dbo.udtOrderDetails;

INSERT INTO @OrderDetails(OrderId, ItemId, Quantity)
VALUES (10, 1, 10),
       (10, 2, 20),
       (10, 3, 30),
       (20, 4, 40),
       (20, 5, 50),
       (20, 6, 60),
       (30, 1, 1),
       (30, 5, 5);


EXEC InsertMultipleOrders @Orders, @OrderDetails;
22 rows affected

-- Verify results
SELECT *
FROM dbo.TblOrders As O
JOIN dbo.TblOrderDetails As OD
    ON O.Id = OD.OrderId;


Id
CustomerId
CreateDate
Id
OrderId
ItemId
Quantity


1
1
2020-05-17 17:00:00.0000000
1
1
1
10

1
1
2020-05-17 17:00:00.0000000
2
1
2
20

1
1
2020-05-17 17:00:00.0000000
3
1
3
30

2
1
2020-05-17 17:30:00.0000000
4
2
4
40

2
1
2020-05-17 17:30:00.0000000
5
2
5
50

2
1
2020-05-17 17:30:00.0000000
6
2
6
60

3
1
2020-05-17 18:00:00.0000000
7
3
1
1

3
1
2020-05-17 18:00:00.0000000
8
3
5
5
Id	CustomerId	CreateDate	Id	OrderId	ItemId	Quantity
1	1	2020-05-17 17:00:00.0000000	1	1	1	10
1	1	2020-05-17 17:00:00.0000000	2	1	2	20
1	1	2020-05-17 17:00:00.0000000	3	1	3	30
2	1	2020-05-17 17:30:00.0000000	4	2	4	40
2	1	2020-05-17 17:30:00.0000000	5	2	5	50
2	1	2020-05-17 17:30:00.0000000	6	2	6	60
3	1	2020-05-17 18:00:00.0000000	7	3	1	1
3	1	2020-05-17 18:00:00.0000000	8	3	5	5