CODEWITHSUNDEEP
sql-server
Atabai Fekri
Atabai Fekri

Reputation: 309

How to insert data table by stored procedure in SQL SERVER

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.

Upvotes: 0

Views: 2136

Answers (1)

Dan Guzman
Dan Guzman

Reputation: 46425

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.

Upvotes: 1

Related Questions