Inserting zero in Identity Column

Question

Inserting zero in Identity Column

Viewing 7 posts - 16 through 21 (of 21 total)

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

RBarryYoung SSC Guru Points: 143329 More actions · Answer 1

Has this table been TRUNCATEd?

IIRC, this is a known ~~bug~~feature of truncating a table with an Identity column.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 2

ramadesai108 (3/4/2010)

Just 1 record with zero. If i insert data manully using query analyzer, then the second record is inserted with the identity of 2.

Here is another user who is complaining the same thing:

http://www.generation-nt.com/us/instead-insert-inserted-identity-column-0-help-116980361.html%5B/quote%5D

Not the same from what I can see. That user has an INSTEAD OF trigger defined on the table whereas you indicate that there are no triggers on your table.

ramadesai108 SSChampion Points: 10609 More actions · Answer 3

I create this table thru an application that is using this script. After that within the same application, i add records to the tables. So i do not want to use DBCC Chekident. But what i ended up doing is after i create the tables, i insert a dummy record in each of them and then delete them. Then insert real records and it works fine. It is weird way of solving the problem, but if any one has a better idea, then feel free to suggest.

Thanks.

SQLRNNR SSC Guru Points: 281334 More actions · Answer 4

I think your app is not creating the table as it should. Using DBCC CheckIDent will permit you to verify the identity value of the table before you insert into the table. This would have given evidence that the app is not creating the table properly. I don't think I would allow an application to create the table either, but that is a matter of preference and controls.

Glad you found a work around though.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

philmond Old Hand Points: 360 More actions · Answer 5

philmond

Old Hand

Points: 360

August 23, 2010 at 6:36 am

#1210406

Is your customers table involved in Replication?

shasan SSC Journeyman Points: 98 More actions · Answer 6

Dear Can u please copy the stored procedure with which you are inserting the records into the table are you passing the identity value wile inserting?

spaghettidba SSC Guru Points: 105732 More actions · Answer 7

shasan (1/22/2012)
Dear Can u please copy the stored procedure with which you are inserting the records into the table are you passing the identity value wile inserting?

This thread is two years old, I guess the OP solved this issue long ago.

-- Gianluca Sartori