sql server - C# SqlBulkCopy - Violation of Primary Key constraint - Stack Overflow

admin管理员组

文章数量:1287507

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.

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.

• c#
• sql-server
• primary-key
• sqlbulkcopy

Share Improve this question Follow edited Feb 24 at 13:14 marc_s 755k184184 gold badges1.4k1.4k silver badges1.5k1.5k bronze badges asked Feb 24 at 13:08 GeetarmanGeetarman 1111 bronze badge 13

• Check the log file in the server database by using SQL Server Management Studio and looking at log file in the explorer under Management. The error message are much better in SSMS than in c#. Also with SSMS check the table columns and see if any have primary keys. Then you can use a linq GroupBy on the column and see if any groups contain a count > 1. – jdweng Commented Feb 24 at 13:16
• What's the table definition in regards to the PK columns? And what is the PK? Does something/someone reseed your identities perhaps while you're importing? You can add TABLOCK for better performance and avoidance of the problem perhaps – siggemannen Commented Feb 24 at 13:33
• Thanks for the responses, the database has been set up for my by a DBA in our company so not sure I'll have the relevant access to check the logs etc but will have a look and contact the DBA to check for me. The PK is just an int auto created, from what I can see on properties I have access to it's showing as Data Type = int, System Type = int, Identity Seed = 0, Identoty increment = 0. I'll have a look at TABLOCK and see if that helps thanks for the heads up – Geetarman Commented Feb 24 at 13:45
• 1 What is the PK? Likely it contains something like timestamp(not precise) or some other column that violates the pk constraint. – Anand Sowmithiran Commented Feb 24 at 13:45
• 2 Are you sure the column has an increment of 0? Check the column properties in SSMS or whatever database tools you have or, if you can run a query, use EXEC sp_help 'dbo.mytable' (change dbo to whatever schema your table is in). Several results will be returned, one of these results shows the identity column. Typically the seed is 0 or 1 and the increment is 1. – Alan Schofield Commented Feb 24 at 17:06

 |  Show 8 more comments

1 Answer 1

Sorted by: Reset to default 0

After I ran EXEC sp_help 'dbo.mytable' and noted the PK column error "No identity column defined" our DBA 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.

本文标签： sql serverC SqlBulkCopyViolation of Primary Key constraintStack Overflow