SQL Server: SqlBulkCopy import causes primary key violations

I am trying to design a window based application in C#.NET. I am reading csv file in data grid view and then insert those data into database. I am using SqlBulkCopy to insert data from csv file into database. My concern is, when I am trying to insert data into database (which already consist data) I am getting error of primary key constraint. I want to know whether it is possible to compare value before inserting into database using SqlBul开发者_如何学PythonkCopy. If value exist in database it should do update.

Can anyone provide me logic for this.

Thanks.

If you really, really know that the dupes aren't needed, just set the "Ignore Dupes" option on the index for the PK and you're done.

You need to read the data from the database into a list first. Then when you load the csv you discard any duplicates.

Also, when you insert you should possibly ignore the primary key and let the sql database generate the key.

There's no way to do any logic when you're firehosing data in via SQLBulkCopy. Even triggers (shudder) are turned off.

What you should do is bulkcopy your data into an empty staging table, then run a stored procedure that merges the data from the staging table into the real, non-empty table.

If you're using SQL 2008, then you can use the MERGE command, otherwise you'll just have to code around it with a separate update and insert.