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

ado.net c# primary-key sqlbulkcopy sql-server

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 :)

Popular Answer

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.




Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why