I have a dataTable which I will use to BulkCopy to a destination table. I am trying to resolve primary key violation. So instead of using a temp table and then merge with production, I am trying to delete the redundant rows from the DataTable in memory before passing it to the WriteToServer method.
In my code I create the Memory DataTable columns the in the same exact format as the table in Database in terms of columns and fields. So I am not doing any column mapping. My PK record is a GUID having a unique value constraint and is called PKID. I fill the DataTable from CSV files on disk then do the BulkCopy.
My idea is to try to do the following logic: Delete From MemoryDataTable where PKID is in (SELECT PKID from SQLTable)
Here is my code:
Try Using sqlBulk As New SqlBulkCopy(LocalDBConnectionString, SqlBulkCopyOptions.TableLock) sqlBulk.DestinationTableName = "DataRecords" sqlBulk.BatchSize = 5000 sqlBulk.WriteToServer(MemoryDataTable) sqlBulk.Close() End Using Catch ex As Exception EventArgs.ErrorMessage = ex.Message ''''Catch Primary Key Violation Here'''' End Try
Thanks that can be a good option. However, I did accomplish what I was trying to do :) In the catch section I selected all PKs from the live DB table into a DataReader. Then I loop through the reader returned rows. In each loop I use the MemoryDataTable.rows.find method to check whether the the DataReader row exists in the MemoryDataTable, if yes I delete it.