How to insert data table by stored procedure in SQL SERVER

Question

tried to insert many records of a data table to SQL table by stored procedure.I need insert data table's records all together.

I'm using C# programming language and i want to send many records to SQL server by ADO.net stored procedure all together. i want to know about data table types and use it if that helps me.

Possible duplicate of [Insert 2 million rows into SQL Server quickly](https://stackoverflow.com/questions/13722014/insert-2-million-rows-into-sql-server-quickly) — Serg, Jun 02 '19 at 12:00
https://blogs.msdn.microsoft.com/nikhilsi/2008/06/11/bulk-insert-into-sql-from-c-app/ — NBaua, Jun 02 '19 at 12:07

Dan Guzman · Accepted Answer · 2019-06-02T12:23:16.413

To pass many rows efficiently to a stored procedure, use a table-valued parameter. The C# app can specify parameter type structured and a value of a data table. For maximum performance, make sure the DataTable column types match the server-side table type column types (including max length for string columns).

Below is an example excerpt from the documentation link above:

// Assumes connection is an open SqlConnection object.  
using (connection)  
{  
  // Create a DataTable with the modified rows.  
  DataTable addedCategories = CategoriesDataTable.GetChanges(DataRowState.Added);  

  // Configure the SqlCommand and SqlParameter.  
  SqlCommand insertCommand = new SqlCommand("usp_InsertCategories", connection);  
  insertCommand.CommandType = CommandType.StoredProcedure;  
  SqlParameter tvpParam = insertCommand.Parameters.AddWithValue("@tvpNewCategories", addedCategories);  
  tvpParam.SqlDbType = SqlDbType.Structured;  

  // Execute the command.  
  insertCommand.ExecuteNonQuery();  
}

Here are T-SQL snippets to create the table type and proc.

CREATE TYPE dbo.CategoryTableType AS TABLE  
    ( CategoryID int, CategoryName nvarchar(50) );
GO

CREATE PROC dbo.usp_InsertCategories
AS
@tvpNewCategories dbo.CategoryTableType READONLY
INSERT INTO dbo.Categories (CategoryID, CategoryName)  
    SELECT nc.CategoryID, nc.CategoryName FROM @tvpNewCategories AS nc;
GO

Even for trivial inserts, TVP performance can provide performance similar to SqlBulkCopy (many thousands per second) with the advantage of a stored procedure interface.

How to insert data table by stored procedure in SQL SERVER

1 Answers1