One-to-Many Inserts with Table-Valued Parameters

/* Create some tables to work with */
CREATE TABLE dbo.orders
(
      order_id      int Identity(1,1)   Not Null
    , orderDate     date                Not Null
    , customer_id   int                 Not Null
 
    CONSTRAINT PK_orders
        PRIMARY KEY CLUSTERED(order_id)
);
 
CREATE TABLE dbo.orderDetails
(
      orderDetail_id    int Identity(1,1)   Not Null
    , order_id          int                 Not Null
    , lineItem          int                 Not Null
    , product_id        int                 Not Null
 
    CONSTRAINT PK_orderDetails
        PRIMARY KEY CLUSTERED(orderDetail_id)
 
    CONSTRAINT FK_orderDetails_orderID
        FOREIGN KEY(order_id)
        REFERENCES dbo.orders(order_id)
);
 
 
/* Create our new table types */
CREATE TYPE dbo.orderTable AS TABLE 
( 
      orderDate     date
    , customer_id   int
);
GO
 
CREATE TYPE dbo.orderDetailTable AS TABLE 
( 
      lineItem      int
    , product_id    int
);
GO
 
 
/* Let's check out our new data types */
SELECT *
FROM sys.types
WHERE [name] IN ('orderTable', 'orderDetailTable');
GO
 
 
/* Create a new procedure using a table-valued parameter */
CREATE PROCEDURE dbo.insert_orderTVP_sp
      @myOrderTable orderTable READONLY
    , @myOrderDetailTable orderDetailTable READONLY
AS
BEGIN
 
    SET NOCOUNT ON;
 
    DECLARE @myOrderID int;
 
    INSERT INTO dbo.orders
    SELECT orderDate
        , customer_id
    FROM @myOrderTable;
 
    SET @myOrderID = SCOPE_IDENTITY();
 
    INSERT INTO dbo.orderDetails
    SELECT @myOrderID
        , lineItem
        , product_id
    FROM @myOrderDetailTable;
 
    SET NOCOUNT OFF;
 
END
GO
 
 
/* Call our new proc! */
DECLARE @myTableHeaderData AS orderTable
    , @myTableDetailData As orderDetailTable;
 
INSERT INTO @myTableHeaderData
(orderDate, customer_id)
SELECT GETDATE(), 101;
 
INSERT INTO @myTableDetailData
(lineItem, product_id)
SELECT 10, 123 UNION ALL
SELECT 20, 456 UNION ALL
SELECT 30, 789;
 
EXECUTE dbo.insert_orderTVP_sp 
      @myTableHeaderData
    , @myTableDetailData;
 
 
/* Check our data */
SELECT * FROM dbo.orders;
SELECT * FROM dbo.orderDetails;
 
 
/* Clean up our mess */
DROP PROCEDURE insert_orderTVP_sp;
DROP TABLE dbo.orderDetails;
DROP TABLE dbo.orders;
DROP TYPE orderTable;
DROP TYPE orderDetailTable;