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; |
Pingback: Bulk Inserts with XML : SQL Fool
Pingback: Metadata for Table Valued Parameters « SQL Fool