I am going to use SqlBulkCopy, but I need to prevent duplicates insert. What is the best way to do it? Have I do check existence for each item before make execute WriteToServer command?
Bring all the values form the table into memory, you can than meake sure no already existing items are inserted using Except. Something like:
var existingValues = GetAllValuesFromDb();
var newValues = GetNewValues(); //Add .Distinct() to remove duplicates from the new values
var valuesToInsert = newValues.Except(existingValues);
you do not really specify what is your source. If it is a flat file you could SqlBulkCopy into a staging table and afterwards use a
insert into sql statement that filters out the duplicates.