Insert multiple parents with multiple children in a single stored procedure (SQL Server)

#sql #database

(First published on What the # do I know?)

TL;DR There’s a full demo on Db<>Fiddle, and as an answer I’ve posted back in 2016 on stackoverflow.

Let’s say you have an online store database.
Such a database will most likely have some form of orders table, and that orders table will most likely have a related table for the order details.
A very common pattern will look something like this:

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, 
    -- other related columns such as price per unit 
    -- (at the time of the order) etc'
);

Note: There are a few assumptions here – such as that a there’s a Customers table with an int primary key called Id, that there’s an Items table (with a similar primary key) etc’.
These assumptions are not relevant to this post, but if you would try to copy the code, you’ll need these tables.

Usually, adding an order is a very straight forward procedure: You insert one order, grab it’s Id using either the output clause or scope_identity(), and using that value you insert the order details. Of course, you should use a transaction to wrap these two inserts together.

But what if you need to insert multiple orders at once?
Well, to do that, you use a stored procedure with table valued parameters and a Merge statement:

Here’s the trick: You populate two table valued parameters with the orders and order details – and you give them temporary ids. Note that the temporary ids must tie the records in the order details to their respective parent records in the orders table.
Then, you use Merge (instead of Insert) to insert the data to the orders table – because when you use Merge you can output data from both the source and the target table.

So – let’s start by creating the user defined table types that we’re going to need in order for this procedure: We will need one table type for the orders, and another one for the order details.

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

Now that we have the user defined table types that matches the tables we want to populate, we can write the stored procedure:

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

The key point of this stored procedure is the usage of the Output clause of the Merge statement to populate the @Map table variable – we are mapping the temporary id that came in with the data to the actual id that was generated by the database.

Once we have the mappings between the temporary ids and the database-generated ids, all we have to do is insert the order details using a join between the @OrderDetails table valued parameter and the @Map table variable.