Insert entire DataTable into database at once instead of row by row?

by

I discovered SqlBulkCopy is an easy way to do this, and does not require a stored procedure to be written in SQL Server.

Here is an example of how I implemented it:

// take note of SqlBulkCopyOptions.KeepIdentity , you may or may not want to use this for your situation.  

using (var bulkCopy = new SqlBulkCopy(_connection.ConnectionString, SqlBulkCopyOptions.KeepIdentity))
{
      // my DataTable column names match my SQL Column names, so I simply made this loop. However if your column names don't match, just pass in which datatable name matches the SQL column name in Column Mappings
      foreach (DataColumn col in table.Columns)
      {
          bulkCopy.ColumnMappings.Add(col.ColumnName, col.ColumnName);
      }

      bulkCopy.BulkCopyTimeout = 600;
      bulkCopy.DestinationTableName = destinationTableName;
      bulkCopy.WriteToServer(table);
}

More Related Contents:

  1. How can I round a value in SQL
  2. How do I update a value in C# and SQL Server? [closed]
  3. Delete from two tables? [closed]
  4. Execute Insert command and return inserted Id in Sql
  5. Adding multiple parameterized variables to a database in c#
  6. Getting return value from stored procedure in C#
  7. SQL error: Incorrect syntax near the keyword ‘User’
  8. Passing List to SQL Stored Procedure
  9. SQL WHERE clause matching values with trailing spaces
  10. Query extremely slow in code but fast in SSMS
  11. How to retrieve data from a SQL Server database in C#?
  12. Code to validate SQL Scripts
  13. Any way to SQLBulkCopy “insert or update if exists”?
  14. SQLException : String or binary data would be truncated
  15. Multi threading C# application with SQL Server database calls
  16. SqlConnection.Close() inside using statement
  17. Generate class from database table
  18. ALTER TABLE DROP COLUMN failed because one or more objects access this column
  19. When executing a stored procedure, what is the benefit of using CommandType.StoredProcedure versus using CommandType.Text?
  20. Can I use ADFS 2.0 to authenticate certain users against SQL Server?
  21. ExecuteNonQuery returning -1 when using sql COUNT despite the query string
  22. What is passing parameters to SQL and why do I need it?
  23. The variable name ‘@’ has already been declared. Variable names must be unique within a query batch or stored procedure
  24. How to pass a null variable to a SQL Stored Procedure from C#.net code
  25. How to insert data into SQL Server
  26. Use SQL Server time datatype in C#.NET application?
  27. Database Error: There is no row at position 0
  28. How to give ADO.NET Parameters
  29. Capturing count from an SQL query
  30. Is SqlConnection.Close() need inside a Using statement?

Leave a Comment