create type CustomerUdt as table
(
Id int,
CustomerName nvarchar(50),
PostalCode nvarchar(50)
)
and then utilize that in a stored procedure like so...
create procedure InsertCustomerInfo
(
@CustomerInfoTvp as CustomerUdt readonly
)
as
insert into [Customers]
select * from @CustomerInfoTvp
this will do a bulk insertion for you... very nice!
the schema that you setup in the user-defined table type has to be the same as [Customers] in the example above.
This works best in the case where you are make multiple calls to the database for each row of data to separate tables. You can combine user-defined table types in one stored procedure like so...
create procedure InsertNewOrder
(
@OrderTvp as OrderUdt readonly,
@OrderDetailTvp as OrderDetailsUdt readonly
)
insert into [Orders]
select * from @OrderTvp
insert into [OrderDetails]
select * from @OrderDetailTvp
That makes one call to the database. Easy peasy.