SqlBulkCopy import causes primary key violations in SQL Server

c# sqlbulkcopy sql-server

Question

In C#.NET, I'm attempting to create a window-based application. I'm reading data from a csv file into a data grid view before inserting it into a database. I useSqlBulkCopy to database to insert data from csv file. My main worry is that I keep receiving primary key constraint errors when I attempt to put data into databases that already contain data. If it's feasible to compare values before putting them into a database utilizingSqlBulkCopy Update should occur if the value is present in the database.

Can someone explain the rationale behind this?

Thanks.

1
1
3/12/2011 10:47:07 PM

Fastest Entity Framework Extensions

Bulk Insert
Bulk Delete
Bulk Update
Bulk Merge

Popular Answer

Set the "Ignore Dupes" option on the index for the PK if you are absolutely certain that the dupes are not required.

2
3/13/2011 6:32:11 AM

Related Questions

With the SqlBulkCopy class, you may import data from SQLite to SQL Server.

It's possible that this will cure your issue, but you should definitely use the SqlBulkCopyOptions to tell it not to create any new identity values....SqlBulkCopy bulkCopy = new SqlBulkCopy(connectionString, SqlBulkCopyOptions.KeepIdentity))
sqlbulkcopy sqlite
asked by Adam Hughes

When there is no primary key, how can SqlBulkCopy prevent duplicate entries from being inserted?

Each of the thousands of items in the daily XML file I get is a business transaction that I must save in a private database for the purposes of reporting and invoicing. I thought that each day's file only included unique entries, but I've now learned that the provider's definition of unique is different from mine....The application now in use that ...
c# sql sqlbulkcopy sql-server sql-server-2008
asked by kscott

On SQL Server 2000, SqlBulkCopy creates a deadlock.

Finally, I was able to download a 50 gigabyte local copy of our production database to test the program. It turned out that the dealocking only had environmental implications. Thank you, guys.
.net asp.net sql sqlbulkcopy sql-server
asked by stevenjmyu

Importing XML data into a SQL database using sqlBulkCopy

I normally dislike importing XML data into my database using a ...DataSet...; the...DataSet... class has a highly particular method for organizing its data as XML and reading other XML in the opposite direction....Having said that, I prefer to use the ...WriteToServer... overload... implementation, which requires ...IDataReader.......It's simple t...
.net dataset sqlbulkcopy xml xmldocument
asked by casperOne

How to extract primary key from SqlBulkCopy when inserting numerous rows in EF is too slow?

In one of our use cases, a user initiates a request that leads to the insert of 100–1000 rows.... The Primary Key ID of the inserted objects is required in order to process the object further and produce other objects that serve as foreign keys to the original inserted objects once it has been inserted....This has been done so far using EF in a for...
entity-framework sql sqlbulkcopy
asked by Jonathan Levison

SQL Server import faster than bulk import

I want to import a huge (around 50 GB, about 300 million rows) tab separated file into a SQL Server table with columns....char(10), varchar(512), nvarchar(512), nvarchar(512)... ....The bulk import process using T-SQL, SSIS, or C...SqlBulkCopy... #'s class takes around a day....Exists a quicker method to load this data?...Or maybe there's a problem...
bulkinsert sqlbulkcopy sql-server
asked by Egor Antonov

How to do PostgreSQL Bulk INSERT without Primary Key Violation

In most cases, I'd create a second staging table for this kind of circumstance that does not have the PK constraint, and I'd fill it using...COPY... (Assuming the data are in a format where a comparison makes sense)...COPY... Then I'd do the following)....insert into table select a.* from staging a where not exists (select 1 from ...
npgsql postgresql sqlbulkcopy
asked by yieldsfalsehood

Overpassing SqlBulkCopy - Violation of Primary Key

The possibility of a duplication determines the technique that will perform the best. If duplicates are infrequent, you may start with a bulk copy before switching to single-row conditional inserts or merging if a PK violation occurs....A stored procedure that accepts a table-valued argument of type IEnumerable SqlDataRecord and inserts rows condit...
sqlbulkcopy sql-server vb.net
asked by Dan Guzman

Importing user-friendly Excel sheet into a SQL Server table that has many foreign keys

In this case, I would be developing an SSIS package. SSIS may receive data from Excel and be instructed to query a database for additional data in order to create a valid dataset that complies with FK requirements.
c# excel sqlbulkcopy sqldataadapter sql-server
asked by DeanOC

SQL bulk copy for native clients causes access violation

The usage of "SQL server" ODBC connections when the bulk copy methods are attempting to utilize native connections seems to be the cause of the issue....The bulk copy procedures function well if I switch all other ODBC connections to SQL Native.
c++ sqlbulkcopy sql-server
asked by Simon Callan

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