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
CREATE TYPE dbo.orderDetailTable AS TABLE 
      lineItem      int
    , product_id    int
/* Let's check out our new data types */
FROM sys.types
WHERE [name] IN ('orderTable', 'orderDetailTable');
/* Create a new procedure using a table-valued parameter */
CREATE PROCEDURE dbo.insert_orderTVP_sp
      @myOrderTable orderTable READONLY
    , @myOrderDetailTable orderDetailTable READONLY
    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;
/* Call our new proc! */
DECLARE @myTableHeaderData AS orderTable
    , @myTableDetailData As orderDetailTable;
INSERT INTO @myTableHeaderData
(orderDate, customer_id)
INSERT INTO @myTableDetailData
(lineItem, product_id)
SELECT 30, 789;
EXECUTE dbo.insert_orderTVP_sp 
    , @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;
If you enjoyed this post, please consider leaving a comment or subscribing to the RSS feed to have future articles delivered to your feed reader.
Tagged , . Bookmark the permalink.

3 Responses to One-to-Many Inserts with Table-Valued Parameters

  1. Pingback: Bulk Inserts with XML : SQL Fool

  2. Pingback: Metadata for Table Valued Parameters « SQL Fool

  3. sarkaut says:

    plaese help me
    i want insert two table at same time (one to many)
    used sql server 2008 and and c#(visual studio 2010)
    for project shopping for condtional
    two table ,one table reklam one insert for table image multi image insert

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>