How to insert a data table into SQL Server database table?

by

Create a User-Defined TableType in your database:

CREATE TYPE [dbo].[MyTableType] AS TABLE(
    [Id] int NOT NULL,
    [Name] [nvarchar](128) NULL
)

and define a parameter in your Stored Procedure:

CREATE PROCEDURE [dbo].[InsertTable]
    @myTableType MyTableType readonly
AS
BEGIN
    insert into [dbo].Records select * from @myTableType 
END

and send your DataTable directly to sql server:

using (var command = new SqlCommand("InsertTable") {CommandType = CommandType.StoredProcedure})
{
    var dt = new DataTable(); //create your own data table
    command.Parameters.Add(new SqlParameter("@myTableType", dt));
    SqlHelper.Exec(command);
}

To edit the values inside stored-procedure, you can declare a local variable with the same type and insert input table into it:

DECLARE @modifiableTableType MyTableType 
INSERT INTO @modifiableTableType SELECT * FROM @myTableType

Then, you can edit @modifiableTableType:

UPDATE @modifiableTableType SET [Name] = 'new value'

More Related Contents:

  1. Creating a SQL Server table from a C# datatable
  2. Print Contents Of A DataTable
  3. run .sql file over a collection of databases on an instance
  4. How to pass an array into a SQL Server stored procedure
  5. Call a stored procedure with parameter in c#
  6. Execute a large SQL script (with GO commands)
  7. Calling stored procedure with return value
  8. Cannot open database “test” requested by the login. The login failed. Login failed for user ‘xyz\ASPNET’
  9. Sorting rows in a data table
  10. Using StringWriter for XML Serialization
  11. Read SQL Table into C# DataTable
  12. ExecuteNonQuery: Connection property has not been initialized.
  13. Entity framework very slow to load for first time after every compilation
  14. Avoiding SQL injection without parameters
  15. How can I lock a table on read, using Entity Framework?
  16. How to convert datatable to json string using json.net?
  17. Efficient DataTable Group By
  18. .NET – Convert Generic Collection to DataTable
  19. Exception using CopyToDataTable with “new {..}” LINQ query
  20. Connection timeout for SQL server
  21. Calculated column in EF Code First
  22. MultipleActiveResultSets=True or multiple connections?
  23. Generate class from database table
  24. What is passing parameters to SQL and why do I need it?
  25. SqlBulkCopy – The given value of type String from the data source cannot be converted to type money of the specified target column
  26. Is it better to execute many sql commands with one connection, or reconnect every time?
  27. Upload CSV file to SQL server
  28. SQL Server: Dynamic where-clause
  29. Watch for a table new records in sql database
  30. Convert DataTable to Generic List in C#

Leave a Comment