Identity column insert issue

Question

Identity column insert issue

sqlnaive

SSCoach

Points: 17437
More actions
April 28, 2015 at 3:18 am

#296360

I have two tables having one row identifier column each of int datatype. Both these columns are part of the respective primary keys. Now as a part of my process, i'm inserting one small part of data from one table to another table. This was working fine but suddenly started getting error like
Violation of PRIMARY KEY constraint 'PK_TargetTable'. Cannot insert duplicate key in object 'DW.TargetTable'. The duplicate key value is (58544748).
First I checked with DBCC CHECKIDENT with NORESEED and found that there is difference in the current identity value and current column value. I fixed it by running DBCC CHECKIDENT. But to my surprise again got the same issue. interesting thing is that the error comes after inserting 65466 records.
Has anyone seen such behaviour ?

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 1

You haven't given us much to work on. How similar are the tables supposed to be?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Grant Fritchey SSC Guru Points: 398909 More actions · Answer 2

Check the data and your code again. If you're getting a primary key violation, especially while doing identity insert, you're attempting to add a value that already exists.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Kenny Jozi SSCrazy Points: 2028 More actions · Answer 3

try to use IF NOT EXISTS when doing your insert

SQL 2000/2005/2008/2012 DBA - MCTS/MCITP

sqlnaive SSCoach Points: 17437 More actions · Answer 4

I have somewhat found what's going on here. The two tables i'm talking here let's say TableA & TableB. The column name of the identity column is row_num_id. As part of our application process, the data gets loaded from a file to our staging area and after data cleansing stored in a table say Table_Initial. This table Table_Initial has same structure as TableA including that identity column. Now we switch the partition from Table_Initial to TableA. And here what I got to know is that in this scenario identity column allows same ids to be inserted in TableA. It somehow changes the identity column config for this table.

Now when a part of data is getting loaded from TableB to TableA, then it checks the identity column. And because it got modified probably to a lower number than the current maximum value, it tries to insert data with already existing value and flags error, Hope i'm not confusing you guys with the description of scenario. Now why partition switch can cause identity reseed, thats the question.