Delete rows from DataTable in memory matching duplicate rows in Database

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

score 0 · Answer 1 · answered Aug 25 '14 at 21:39

You can't do this as the MemoryDataTable only exists within the context of your vb.net program i.e. SQL/Server knows nothing about it.

However, you can load you memory table up to a temporary table on the server and then do whatever SQL operations you like on it. E.g.

Select * into #TempDataTable From SQLTable

then do your SqlBulk load into the #TempDataTable and either 1) Merge #TempDataTable into SQLTable or do insert/delete operations. Note the # - this makes the table a temporary one that would be cleared up automatically if you do not drop it after using it. It is however good practice to Drop temporary tables when you are finished with them.

I really recommend that you use a MERGE statement, it is much more efficient than delete's and inserts.

score 0 · Answer 2 · answered Aug 28 '14 at 13:21

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.

Delete rows from DataTable in memory matching duplicate rows in Database

2 Answers2