Violation of PRIMARY KEY constraint. Cannot insert duplicate key in object

Question

I am dumping millions of records in my table using sql bulk copy.I process my data and prepare datatable and once size reach to 1000 records i use Sql Bulk copy to dump data in table and then empty datatable and this process goes on.

At the end i commit transaction in the End method and dispose off bulk copy,transaction etc..

When i try to dump records i am getting this error :

Violation of PRIMARY KEY constraint 'PK_Sales'. Cannot insert duplicate key in object 'dbo.Sales'. The duplicate key value is (10364).\r\nThe statement has been terminated

I have columns ordering same as my database table column ordering.I even did truncate statement and also executed this statement :

DBCC CHECKIDENT(dbo.Discrepancy, RESEED, 0);

I got this output :

Checking identity information: current identity value '0'.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

But still same error even after doing above.

I then tried to insert record from sql server management studio and it got inserted but value of Id(pk) is to my surprise is : 10365

Structure of Sales table :

Id(pk)   TestId(F.K)     othercols

Note : I have set AutoIncrement on my Id column.

Code :

public class SaveRepo : IDisposable
    {
        DataTable dataTable;
        SqlConnection connection;
        string connectionString;
        SqlTransaction transaction;
        SqlBulkCopy bulkCopy;
        int testId,

        public SaveRepo (int testId)//testId=10364
        {
            this.connectionString = connectionString;
            dataTable = new DataTable();
            connection = new SqlConnection(connectionString);
            connection.Open();
            transaction = connection.BeginTransaction();
            bulkCopy = new SqlBulkCopy(connection, SqlBulkCopyOptions.KeepIdentity, transaction);
            bulkCopy.DestinationTableName = "dbo.Sales";
            this.testId = testId;
            dataTable.Columns.Add("TestId", typeof(int));
        }

       void Dump()
        {
            bulkCopy.WriteToServer(dataTable);
        }

        public void End()
        {
            transaction.Commit();
            //dispose the stuffs also
        }
    }

I did search for this issue and most the soltution was revolving around setting Id to Autoincrement which i already have on my column so i am not getting now whats the problem.

I will appreciate any help :)

ILoveStackoverflow ILoveStackoverflow · Accepted Answer · 2018-04-25T12:49:15

I figured out and the problem was SqlBulkCopyOptions.KeepIdentity which was stopping database from generating identity value for the AutoIncrement column Id

I switched to using SqlBulkCopyOptions.Default which solves this problem.

So when you have the destination table assign the identity, do not use the SqlBulkCopyOptions.KeepIdentity option.

Violation of PRIMARY KEY constraint. Cannot insert duplicate key in object

1 Answers