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
15
votes
3 answers

How does SqlBulkCopy Work

I am familiar with the C# SqlBulkCopy class where you can call the 'WriteToServer' method passing through a DataTable. My question is what underlying mechanism in SQL server is used to bulk insert that data? The reason I ask is that the bulk insert…
peter
  • 13,009
  • 22
  • 82
  • 142
14
votes
4 answers

How to add CsvHelper records to DataTable to use for SqlBulkCopy to the database

I am trying to read a CSV file with CsvHelper, load each record into a DataTable, and then use SqlBulkCopy to insert the data into a database table. With the current code, I get an exception when adding a row to the DataTable. The exception is:…
Justin Nafe
  • 3,002
  • 2
  • 18
  • 16
13
votes
2 answers

SqlBulkCopy: What is the difference between passing SqlBulkCopyOptions.UseInternalTransaction and not passing it?

I am trying to find the difference between using SqlBulkCopy with the SqlBulkCopyOptions.UseInternalTransaction copy option and without it, but in my test application I do not detect any difference. If BatchSize is for example 0 and I add 100…
Jürgen Bayer
  • 2,993
  • 3
  • 26
  • 51
12
votes
2 answers

Can't insert data table using sqlbulkcopy

This is my code with the following columns and in the DB, those columns are nvarchars. SqlBulkCopy bulkCopy = new SqlBulkCopy(connection, System.Data.SqlClient.SqlBulkCopyOptions.Default, transaction); bulkCopy.DestinationTableName =…
Rocshy
  • 3,391
  • 11
  • 39
  • 56
12
votes
3 answers

How to automatically truncate string when do bulk insert?

I want to insert many rows (constructed from Entity Framework objects) to SQL Server. The problem is, some of string properties have length exceeded length of column in database, which causes an exception, and then all of rows will unable to insert…
Delta76
  • 13,931
  • 30
  • 95
  • 128
12
votes
1 answer

Using SqlBulkCopy, how do I insert data into a table in a non-default database schema?

I need to insert data into a table in a schema named Staging using SqlBulkCopy. It appears the API only allows you to set the target table name by using the DestinationTableName property. How do I accomplish this? Is it possible?
Jon Seigel
  • 12,251
  • 8
  • 58
  • 92
12
votes
1 answer

How to use SqlBulkCopyColumnMappingCollection?

I want to make one SqlBulkCopy method that I can use for all my bulk inserts by passing in specific data through the parameters. Now I need to do mapping on some of them. I don't know how to make a SqlBulkCopyColumnMappingCollection since that was…
chobo2
  • 83,322
  • 195
  • 530
  • 832
11
votes
6 answers

Fastest way to insert into a SQL Server table from .NET code?

What is the fastest way to do this: One table, no references that I cannot prefill (i.e. there is one reference key there, but i have all the data filled in) LOTS of data. We talk of hundreds of millions of rows per day, coming in dynamically…
TomTom
  • 61,059
  • 10
  • 88
  • 148
11
votes
2 answers

How do I capture the data passed in SqlBulkCopy using the Sql Profiler?

I am using Sql Profiler all the time to capture the SQL statements and rerun problematic ones. Very useful. However, some code uses the SqlBulkCopy API and I have no idea how to capture those. I see creation of temp tables, but nothing that…
mark
  • 59,016
  • 79
  • 296
  • 580
10
votes
2 answers

Fast and simple way to import csv to SQL Server

We are importing a csv file with CSVReader then using SqlBulkCopy to insert that data into SQL Server. This code works for us and is very simple, but wondering if there is a faster method (some of our files have 100000 rows) that would also not get…
Austin Harris
  • 5,150
  • 6
  • 26
  • 39
10
votes
1 answer

Rollback for bulk copy

I have an application that make a copy from my database by bulk copy class in c#. Can I rollback the bulk copy action in sql server when occur an exception?
masoud ramezani
  • 22,228
  • 29
  • 98
  • 151
10
votes
2 answers

SqlBulkCopy.WriteToServer not reliably obeying BulkCopyTimeout

I need to count sequential timeout exceptions from SqlBulkCopy. To test this, I use an external app to start a transaction & lock up the target table. Only on the first call does SqlBulkCopy throw a timeout exception when expected. We've tried…
Paul Smith
  • 3,104
  • 1
  • 32
  • 45
10
votes
3 answers

SqlBulkCopy Error handling / continue on error

I am trying to insert huge amount of data into SQL server. My destination table has an unique index called "Hash". I would like to replace my SqlDataAdapter implementation with SqlBulkCopy. In SqlDataAapter there is a property called…
Paladin
  • 113
  • 1
  • 1
  • 6
9
votes
2 answers

What's the drawback of SqlBulkCopy

I have done some research for "The bast way to insert huge data into DB with C#" then a lot of people just suggested me using SqlBulkCopy. After I tried it out and it really amazed me. Undoubtedly, SqlBulkCopy is very very fast. It seems that…
Edison Chuang
  • 2,911
  • 4
  • 26
  • 28
9
votes
5 answers

Select into statement where source is other database

How to copy data from one DB into another DB with the same table structure and keep the key identities? I use Sql Server 2012 "Denali" and I want to copy some data from a Sql Server 2008 DB. The tables I have are exactly the same but I want the data…
Marthin
  • 6,413
  • 15
  • 58
  • 95
1 2
3
…
64 65