Insert ADO.Net DataTable into an SQL table
11,952
Solution 1
You can pass the entire DataTable as a single Table Valued Parameter and insert the entire TVP at once. The following is the example from Table-Valued Parameters in SQL Server 2008 (ADO.NET):
// Assumes connection is an open SqlConnection.
using (connection)
{
// Create a DataTable with the modified rows.
DataTable addedCategories = CategoriesDataTable.GetChanges(
DataRowState.Added);
// Define the INSERT-SELECT statement.
string sqlInsert =
"INSERT INTO dbo.Categories (CategoryID, CategoryName)"
+ " SELECT nc.CategoryID, nc.CategoryName"
+ " FROM @tvpNewCategories AS nc;"
// Configure the command and parameter.
SqlCommand insertCommand = new SqlCommand(
sqlInsert, connection);
SqlParameter tvpParam = insertCommand.Parameters.AddWithValue(
"@tvpNewCategories", addedCategories);
tvpParam.SqlDbType = SqlDbType.Structured;
tvpParam.TypeName = "dbo.CategoryTableType";
// Execute the command.
insertCommand.ExecuteNonQuery();
}
TVP are only available in SQL 2008.
Solution 2
I think you are looking for SQLBulkCopy.
Author by
OrElse
Updated on July 17, 2022Comments
-
OrElse almost 2 years
The current solution i implemented is awful!
I use a
for... loop
for inserting records from an ADO.NET data-table into an SQL table.I would like to insert at once the data-table into the SQL table, without iterating...
Is that possible, or am i asking too much?