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

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