Stack Overflow
Stack Overflow

Questions tagged [sqlbulkcopy]

Lets you efficiently bulk load a SQL Server table with data from another source.

The SqlBulkCopy class can be used to write data only to SQL Server tables. But the data source is not limited to SQL Server; any data source can be used, as long as the data can be loaded to a DataTable instance or read with a IDataReader instance.

Using the SqlBulkCopy class, you can perform:

  • A single bulk copy operation
  • List item Multiple bulk copy operations
  • List item A bulk copy operation within a transaction

Link to Microsoft Help Documentation: Microsoft SqlBulkCopy Documentation

964 questions
79
votes
9 answers

SqlBulkCopy Insert with Identity Column

I am using the SqlBulkCopy object to insert a couple million generated rows into a database. The only problem is that the table I am inserting to has an identity column. I have tried setting the SqlBulkCopyOptions to SqlBulkCopyOptions.KeepIdentity…
FlyingStreudel
  • 4,434
  • 4
  • 33
  • 55
78
votes
11 answers

SqlBulkCopy - The given value of type String from the data source cannot be converted to type money of the specified target column

I'm getting this exception when trying to do an SqlBulkCopy from a DataTable. Error Message: The given value of type String from the data source cannot be converted to type money of the specified target column. Target Site: System.Object…
Ricketts
  • 5,025
  • 4
  • 35
  • 48
76
votes
6 answers

Bulk inserts taking longer than expected using Dapper

After reading this article I decided to take a closer look at the way I was using Dapper. I ran this code on an empty database var members = new List(); for (int i = 0; i < 50000; i++) { members.Add(new Member() { Username =…
kenwarner
  • 28,650
  • 28
  • 130
  • 173
41
votes
3 answers

Get an IDataReader from a typed List

I have a List with a million elements. (It is actually a SubSonic Collection but it is not loaded from the database). I'm currently using SqlBulkCopy as follows: private string FastInsertCollection(string tableName, DataTable…
Jason Kealey
  • 7,988
  • 11
  • 42
  • 55
38
votes
3 answers

Is it possible to use System.Transactions.TransactionScope with SqlBulkCopy?

Very simple question: is it possible to use System.Transactions.TransactionScope together with SqlBulkCopy? The documentation Transaction and Bulk Copy Operations doesn't mention anything (at least as of .NET 4.0) and my testing indicates it does…
jason
  • 236,483
  • 35
  • 423
  • 525
37
votes
6 answers

Mapping columns in a DataTable to a SQL table with SqlBulkCopy

I would like to know how I can map columns in a database table to the datatable in c# before adding the data to the database. using (SqlBulkCopy s = new SqlBulkCopy(conn)) { s.DestinationTableName = destination; …
user2545743
  • 799
  • 2
  • 8
  • 10
36
votes
12 answers

The given ColumnMapping does not match up with any column in the source or destination

I dont know why I am getting the above exception, please someone look at it .... DataTable DataTable_Time = new DataTable("Star_Schema__Dimension_Time"); DataColumn Sowing_Day = new DataColumn(); Sowing_Day.ColumnName = "Sowing_Day"; DataColumn…
user1056466
  • 597
  • 1
  • 7
  • 17
34
votes
6 answers

Any way to SQLBulkCopy "insert or update if exists"?

I need to update a very large table periodically and SQLBulkCopy is perfect for that, only that I have a 2-columns index that prevents duplicates. Is there a way to use SQLBulkCopy as "insert or update if exists"? If not, what is the most efficient…
Sol
  • 365
  • 1
  • 3
  • 6
33
votes
3 answers

Timeout expired with SqlBulkCopy

I'm using SqlBulkCopy to restore tables from xml backups. One of the table backup is ~200MB large and has a lot of records. I'm having error: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not…
HasanG
  • 12,734
  • 29
  • 100
  • 154
30
votes
2 answers

How does SqlBulkCopy circumnavigate foreign key constraints?

I used SqlBulkCopy to insert a collection of rows into a table. I forgot to set an integer value on the rows. The missing column is used to reference another table and this is enforced with a foreign key constraint. For every row inserted, the final…
Ian Warburton
  • 15,170
  • 23
  • 107
  • 189
29
votes
5 answers

Sql Bulk Copy/Insert in C#

I am new to JSON and SQLBulkCopy. I have a JSON formatted POST data that I want to Bulk Copy/Insert in Microsoft SQL using C#. JSON Format: { "URLs": [{ "url_name": "Google", "url_address": "http://www.google.com/" }, { …
Ali007
  • 648
  • 1
  • 12
  • 20
27
votes
7 answers

Getting SqlBulkCopy to honor column names

I'm in the process of converting some stored procedure based reporting routines to run in C#. The general idea is to use all the wonders of C#/.NET Framework and then blast the results back into the DB. Everything has been going swimmingly, except…
Paul Alan Taylor
  • 10,474
  • 1
  • 26
  • 42
27
votes
7 answers

Possible to get PrimaryKey IDs back after a SQL BulkCopy?

I am using C# and using SqlBulkCopy. I have a problem though. I need to do a mass insert into one table then another mass insert into another table. These 2 have a PK/FK relationship. Table A Field1 -PK auto incrementing (easy to do SqlBulkCopy as…
chobo2
  • 83,322
  • 195
  • 530
  • 832
26
votes
2 answers

SqlBulkCopy - Unexpected existing transaction

I am using SqlBulkCopy to insert large amount of data: try { using (var bulkCopy = new SqlBulkCopy(connection)) { connection.Open(); using (var tran = connection.BeginTransaction(IsolationLevel.ReadCommitted)) { …
1110
  • 7,829
  • 55
  • 176
  • 334
26
votes
5 answers

SqlBulkCopy and DataTables with Parent/Child Relation on Identity Column

We have a need to update several tables that have parent/child relationships based on an Identity primary-key in the parent table, which is referred to by one or more child tables as a foreign key. Due to the high volume of data, we would like to…
James Hugard
  • 3,232
  • 1
  • 25
  • 36
1
2 3
…
64 65