CODEWITHSUNDEEP
c#sql-serverprimary-keysqlbulkcopy
Geetarman
Geetarman

Reputation: 11

C# SqlBulkCopy - Violation of Primary Key constraint

I'm running a C# Winforms application that each month uploads data from a .csv file to a SQL Server database using SqlBulkCopy, but I'm hitting multiple instances of the error:

Violation of PRIMARY KEY constraint 'MainTbl$ID'. Cannot insert duplicate key in object 'dbo.MainTbl'

I've searched the various instances of this error and haven't found one that seems to be the same. My data does not have the PK value included as the server is creating this automatically yet it still seems to be trying to use existing PK values. The data is around 550,000 rows each month with 54 columns.

Here's my code:

private void RunSQLBulkCopy(DataTable table)
{
    GlobalInfo.dbConn.Open();

    using (SqlTransaction transaction = GlobalInfo.dbConn.BeginTransaction())
    {
        using (SqlBulkCopy bulkCopy = new SqlBulkCopy(GlobalInfo.dbConn, SqlBulkCopyOptions.Default, transaction))
        {
            bulkCopy.DestinationTableName = "MainTbl";

            try
            {
                table.Columns.Cast<DataColumn>().ToList().ForEach(x =>
                    bulkCopy.ColumnMappings.Add(new SqlBulkCopyColumnMapping(x.ColumnName, x.ColumnName)));

                bulkCopy.BatchSize = 5000;
                bulkCopy.WriteToServer(table);

                transaction.Commit();
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
                transaction.Rollback();
                UploadFailedTables.Add(table);
            }
        }
    }

    if (GlobalInfo.dbConn.State == ConnectionState.Open)
    {
        GlobalInfo.dbConn.Close();
    }
}

In order to get round the issue for now I've set it up so I'm splitting my upload file into smaller tables then sending them into the upload process above. If the process hits the PK violation then the transaction rolls back and the table is stored in a list which is then re-processed later.

I can get all of the data into the database eventually but it requires multiple runs through , so the data itself seems to be OK but for some reason the auto created PK values seem to be using duplicates. The duplicates are from existing data and not data created during these uploads.

Any suggestion on where I'm going wrong or where else I should look?

From other questions I've read I've tried a few options e.g. from SqlBulkCopyOptions.KeepIdentity to SqlBulkCopyOptions.Default but no change.

Upvotes: 1

Views: 92

Answers (1)

Geetarman
Geetarman

Reputation: 11

So after a load of very useful info in the comments after running the query suggested by @AlanSchofield and noting the PK column error "No identity column defined", I requested help from our DBA who has created a cloned table and defined the PK column as an identity column and now my bulk upload is working with the whole file uploading in one run.

Thanks again to everyone for the help, much appreciated.

Upvotes: 0

Related Questions