ansaurus

Question

Inserting one lakh rows at a time .. error identity column violation

Answer 1

+1 A:

If the destination table is not empty then you want to reseed the identity column to the next highest existing value like so:

Declare @Max bigint
Set @Max = ( Select Max(IdCol) From TableA ) + 1
DBCC CHECKIDENT( TableA, RESEED, @Max )

Thomas 2010-03-06 21:19:07

Thanks Thomas for your reply..couple of questions.. first question is .. why is there a differnce when I first ran this DBCC CHECKIDENT (table1,noreseed)the current identity value and the currnt column are not same.. but after 5 minute when I ran again .. they both are same.. Any idea?Second question.. If I reseed the value.. Hope nothing changes for the existing rows right? It's just that we are going to have new seed value when we insert the next new row? Correct?

Broken Link 2010-03-06 21:21:55

NORESEED simply states that the identity seed should not be changed. Using RESEED you are forcing a new starting value for new identity values. To answer you second question: correct. It will simply start rows at a new value.

Thomas 2010-03-06 21:24:00

Awesome.. Let me try that..

Broken Link 2010-03-06 21:35:12

I haven't executed this solution but I was thinking about this.. When I executed DBCC CHECKIDENT (table1,noreseed) I got the number for example 4000 but when I exucted select max(IdCol) from tableAI got a number 3500 which is less than 4000.This sounds right to me because It could have happened that there are some rows deleted with index 3500 to 4000. First thing is Why am i getting this error? because the next row im giong to insert will have the idcol values as 4000+1.. so why am i getting this number.. even if the new idcol value is going to be max(idcol)+1 then its (cont.,)

Broken Link 2010-03-07 00:27:55

3500+1.. so why am i getting this error in first place.. second how reseeding the value to start from 3500+1 is going to help me?

Broken Link 2010-03-07 00:28:56

The error is that the identity generator is trying to create a value that is already in the table. That means starting at 3500 on, at some point it hits a duplicate. Thus the idea of finding the highest value used and reseeding the identity column. Is this table getting values while you are trying to do this insert? Are there triggers on the table?

Thomas 2010-03-07 01:21:28

I tried this no luck.. I still get the same error. There are no triggers in the DB..

Broken Link 2010-03-07 02:29:31

Just to confirm: No triggers. No other traffic to the table. Identity reseeded to one higher than the highest value in the table. Not inserting into the identity column directly. All of this and it still does not work? Try inserting into a temp table with an identity column seeded exactly like you reseeded the main table and then compare the values in the temp table with those in the main table. That will show you the duplicates and might illuminate where you were getting the collisions.

Thomas 2010-03-07 05:10:29

Superb Idea.. will try it tonight.. Can't thank you more

Broken Link 2010-03-07 06:50:28

Something that should have occurred to me earlier, how can you have an identity seed of 3500 on table with 125 million rows? You reseeded the destination table not the source table?

Thomas 2010-03-07 16:29:50

I gave 3500 as an example. The max value was something around 124million i reseed the value to 124million+1..Also I tried this but didn't work.

Broken Link 2010-03-07 16:56:35

Answer 2

A:

You can use bcp command for this work. You can specify that identity being checked or not.

masoud ramezani 2010-03-07 08:17:34

ansaurus

tags:

views:

answers:

Inserting one lakh rows at a time .. error identity column violation

related questions