Insert/Update Database using SQLBulkCopy

asp.net sqlbulkcopy

Question

I have a datatable with the records.I'm inserting records into Sql table using SqlBulkCopy.It works fine.Next time when get the datatable with same records with few changed values SqlBulkCopy is inserting another set of records without updating the previous details.How can I update the Sql table using SqlBulkCopy ?? Please help.

Thanks, Vix

1
9
11/9/2009 11:36:39 AM

Fastest Entity Framework Extensions

Bulk Insert
Bulk Delete
Bulk Update
Bulk Merge

Expert Answer

Like mentioned by AdaTheDev, SqlBulkCopy can only insert however there is an alternative library which allow to perform Upsert operations.

Disclaimer: I'm the owner of the project Bulk Operations

The Bulk Operations library has a method "BulkMerge" which Insert or Update rows based on the specified key.

var bulk = new BulkOperation(connection);

bulk.ColumnMappings.Add("ID", true);
bulk.ColumnMappings.Add("Column1");
bulk.ColumnMappings.Add("Column2");
bulk.ColumnMappings.Add("Column3");

bulk.BulkMerge(dt);
-1
3/11/2016 6:29:53 PM

Popular Answer

SqlBulkCopy is only used for inserting records, not updating them as explained here. You'd need to use a different technique to do bulk updates.

e.g. you could SqlBulkCopy into a staging table, then run some SQL to update from there to the main table.

AdaTheDev

Related Questions

How can I put data into a table in a non-default database schema using SqlBulkCopy?

While the API does not provide an explicit interface to set the destination schema, you can actually stuff a 2- or 3-part qualified table name into the ...DestinationTableName... property like so:...b.DestinationTableName = string.Format("[{0}].[{1}]", schemaName, tableName); ...or...b.DestinationTableName = string.Format("[{0}].[{1}].[{2}]", d...
.net database-schema schema sqlbulkcopy
asked by Jon Seigel

When using SqlBulkCopy to insert into a sql variant column, how do I specify the column type?

According to the MSDN page for ...SqlBulkCopy... (under "Remarks"):...SqlBulkCopy will fail when bulk loading a DataTable column of type SqlDateTime into a SQL Server column whose type is one of the date/time types added in SQL Server 2008....So, ...SqlBulkCopy... won't be able to handle ...DateTime2... values. Instead, I'd suggest one of two ...
ado.net datatable sqlbulkcopy sql-server sql-variant
asked by chezy525

SQLBulkCopy vs DataAdapter for inserting/update 10lcs records

Are you loading the records into a DataTable just for the insert update? ...If so you would probably be better off just calling the SP directly one at time and skip the DataTable....Or you could use TVP and IEnumerable SqlDataRecord and then you are not limited to 65000. ...An example of using IEnumerable SqlDataRecord
.net dataadapter performance sqlbulkcopy vb.net
asked by paparazzo

C# Bulk Insert SQLBulkCopy - Update if Exists

Thanks to @pst...with his suggestions this is how I implemented, if anyone has to implement similar....Bulk Insert in to permanent Temp Table... using (var bulkCopy = new SqlBulkCopy(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString, SqlBulkCopyOptions.KeepNulls & SqlBulkCopyOptions.KeepIdentity)) { ...
bulkinsert c#-4.0 sqlbulkcopy
asked by HaBo

Inserting unique id for file uploaded to database using SqlBulkCopy

There is a table with the following columns:...ProdID Specification Value ...I've got a csv file with a schema that only has the last two columns - ...Specification... and ...Value.... In other words they are my csv headers. Now I need to associate the values of this file against one unique id....The significance of that id is that it is a referenc...
bulkinsert c# database sqlbulkcopy
asked by deostroll

How to get auto-increment values after inserting records using SqlBulkCopy via c#

I am using ...SqlBulkCopy... class to insert bulk records but i want that newly generated ...auto-increment... values(primary key plays Foreign key in child table) because with the help of that values i am going to play with child tables(Foreign key) making insert....Please follow the below example to get the clear picture,...Source table: Create t...
c# sql sqlbulkcopy stored-procedures
asked by user1531248

Insert Dictionary elements into SQL table as rows using IDatareader and SqlBulkCopy

As you already have figured out, you need to implement custom ...IDataReader.... As your source is Dictionary - only few relatively simple method/properties such as int FieldCount, bool Read(), object Get(int i) should be implemented....You may find useful examples (...a simple one...) of custom IDataReader implementations by googling ...sqlbulkcop...
c# datareader dictionary sql sqlbulkcopy
asked by kasitan

SQLBUlkCopy to call StoredProcedure to insert or update data in SQLDb

SqlBulkCopy... can only insert. If you need to ...upsert..., you might want to ...SqlBulkCopy... into a ...staging table... (a separate table off to one side that isn't part of the main model), and then do the merge in TSQL. You might also want to think about concurrency (how many people can be using the staging table at once, etc).
c# sqlbulkcopy sql-server
asked by Marc Gravell

SQLBulkcopy - Header of datatable getting inserted in the database table

The header is the first row returned from ...rows..., just skip that row when you import....//(snip) dtExcelData.Columns.AddRange(new DataColumn[3] { new DataColumn("AccountNumber", typeof(string)), new DataColumn("Amount", typeof(decimal)), new DataColumn("Sedol",typeof(string)) }); //Makes the header row the current row. ...
asp.net asp.net-mvc c# npoi sqlbulkcopy
asked by Scott Chamberlain

SSIS - OleDB connection not updating data, inserted thru SQLBulkCopy with SQL Connection

My bad....Instead of passing data type as long (int in SQL), i was passing it as Varchar....I was looking from last few hours and as soon as i post question here, it strikes me to check the data type....Hope it will help somebody.
oledbconnection sqlbulkcopy sqlconnection ssis
asked by Manish Joisar

Fastest Entity Framework Extensions

Bulk Insert
Bulk Delete
Bulk Update
Bulk Merge
View more on Stack Overflow

Prime Library

Performance

Expression Evaluator

More Projects...
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow