SQL Server Table Valued Parameters / Types - Multiple Row Inserts

SQL Server 2008 and higher support Table Valued Parameters. TVPs let you marshal multiple rows of data from a client application to your SQL Server with a single trip to the database, without any special server-side logic required to process your data.

Column values in table-valued parameters are accessed using standard T-SQL statements or using CLR routines. You cannot pass TVPs to user defined functions, and TVPs are read-only in SQL Server.

With ADO.NET, Table-valued parameters can be populated with DataTable, DataReader, and IList<SqlDataRecord> objects. You must set the datatype of the parameter to the Structured of the SqlDbType enumeration. If you pass a table-valued parameter to a parameterized SQl Statement, you must specify the previously created type in the SQL Server through the TypeName property of the SqlParameter object.

Here is a short demo of how to insert multiple rows from a DataTable into a SQL Server table using a stored procedure, and ADO.NET:

First, let's set up our TVP Type, Table, and stored proc:

The Database is "TEST".


USE TEST
GO
-- Create the Table
CREATE TABLE TVPTable
(
Id int NOT NULL PRIMARY KEY,
FirstName varchar(40) NULL,
LastName  varchar(40) NULL
)

USE TEST
GO
-- Create the Table  Valued Type
CREATE TYPE TVPTYPE AS TABLE
(
Id int,
FirstName varchar(40),
LastName  varchar(40)
)

USE TEST
GO
-- Create the stored proc for insert
CREATE PROC dbo.InsertTVPTable
(
@tvp TVPType READONLY
)
AS
SET NOCOUNT ON
INSERT INTO TVPTable
SELECT ID, FirstName, LastName FROM @tvp

With this complete, you can run the sample program:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;


namespace TVPTableDemo
{
    class Program
    {
         static void Main(string[] args)
        {
            string cnString = "server=(local);database=TEST;Integrated Security=SSPI";
            DataTable dtTVP = new DataTable();
             dtTVP.Columns.Add("Id", typeof (int));
             dtTVP.Columns.Add("FirstName", typeof(string));
             dtTVP.Columns.Add("LastName", typeof(string));

             for (int i = 1; i < 100; i++){
                 dtTVP.Rows.Add(new object[] {i, "firstname" + i.ToString(), "Lastname" + i.ToString()});
            }

            SqlConnection cn = new SqlConnection(cnString);
            SqlCommand cmd = new SqlCommand("dbo.InsertTVPTable", cn);
            cmd.CommandType = CommandType.StoredProcedure;
            SqlParameter parm = cmd.Parameters.AddWithValue("@tvp", dtTVP);
             cn.Open();
            cmd.ExecuteNonQuery();
            cn.Close();
           Console.WriteLine("TVP INSERT COMPLETED.");

            SqlDataAdapter da = new SqlDataAdapter("SELECT * FROM TVPTABLE", cn);
            DataTable dt = new DataTable();
             da.Fill(dt);
            foreach (DataRow row in dt.Rows)
            {
               Console.WriteLine(row["id"] + ": " + row["FirstName"] + ": " + row["LastName"]);
            }

           Console.WriteLine("Any key to quit.");
           Console.ReadLine();
        }
    }
}

This creates a DataTable with 100 unique rows, passing the DataTable as a SqlParameter to an ADO.NET stored procedure command, and all 100 rows are inserted in a single call to the database.  After the insert is completed, the DataAdapter fills a new table and displays all 100 inserted rows.

You can download the sample code Visual Studio 2010 solution, which includes a file with the above SQL Statements.