Overpassing SqlBulkCopy - Violation of Primary Key

Question

I'm trying into upload to a database records in batches of 10 using SqlBulkCopy. Some of the data already exists in the destination table so, even if one record out of ten exists, the rest fail to get uploaded as well. Is there a way to upload the remaining 9 records?

Try
    Using bulkCopy As SqlBulkCopy = New SqlBulkCopy(conBSD)
       bulkCopy.DestinationTableName = "dbo.cheques2"
        ' Write from the source to the destination.
        bulkCopy.BulkCopyTimeout = 60
        bulkCopy.WriteToServer(dtToUpload)
        iSuccessInserted += 10
        dtToUpload.Clear()
        dtToUpload.Dispose()
        MsgBox("Current " & iCurrent.ToString & " -  " & " In table toUpload " )
   End Using

'Duplicate key, access violation when trying to insert record with same recordID

Catch ex As SqlException When ex.ErrorCode = -2146232060 
   MsgBox(ex.Message & " " & ex.ErrorCode)
   dtToUpload.Clear()
   dtToUpload.Dispose()
   Exit Try
Catch ex As Exception
   MsgBox(ex.Message)
End Try

score 0 · Accepted Answer · answered Oct 15 '14 at 22:31

The best approach from a performance perspective depends on the likelihood if a duplicate. If duplicates are rare, you could try the bulk copy first, and then fall back to single-row conditional inserts or merge in the event of a PK violation.

I've seen excellent performance with a stored procedure that takes a table-valued parameter of type IEnumerable SqlDataRecord that inserts rows conditionally from the TVP. The TVP method internally bulk inserts data into tempdb so it performs very well. If you have duplicates in a source batch, you can de-dup those within the proc too by inserting into a table variable, and using that as the source for the conditional insert or merge. The TVP method will also allow you to increase the batch size and improve performance significantly.

Overpassing SqlBulkCopy - Violation of Primary Key

1 Answers1