Delete rows from DataTable in memory matching duplicate rows in Database

.net datatable sqlbulkcopy sql-server vb.net

Question

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

Popular Answer

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.



Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why