One-to-Many Inserts with Table-Valued Parameters

There’s been much talk about table-valued parameters, but I’ve yet to see an example illustrating one of the greatest potential benefits of this new feature (at least, imho): one-to-many inserts.

But first, for those not yet up to speed on this new feature…

What are Table-Valued Parameters?

According to SQL Books Online 2008:

Table-valued parameters are a new parameter type in SQL Server 2008. Table-valued parameters are declared by using user-defined table types. You can use table-valued parameters to send multiple rows of data to a Transact-SQL statement or a routine, such as a stored procedure or function, without creating a temporary table or many parameters.

That’s great, but why do I care?

This means that a single proc call with table-valued parameters can insert numerous records into one or more tables; for example, inserting a parent record into TableA (header) and several related child records into TableB (details). This has great potential when used with .NET, i.e. a web service that sends sales data to SQL Server.

In 2005, this can be accomplished by using XML and temp tables. My hope is this new feature will outperform the XML method. In my next post, I’ll follow up with a performance comparison to see if table-valued parameters live up to the hype.

In the mean-time, let’s take a look at how we would actually execute this in 2008.

NOTE: This is pseudo-code, so for clarity’s sake, essentials like error handling are absent.

/* 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;