locked
Auto Increment Value RRS feed

  • Question

  • Hi, 

    I have an issue that I need to resolve. I have a table that uses an auto increment value for the primary key.

    I need to insert a couple rows into this table, but I need to enter a specific value for the primary key.

    During maintenance when the server comes offline for a couple mins, I would like to see if I can disable the server protection from  this, and let me manually insert a couple rows with specific primary key values. I know it's not normally possible, but this needs to be done to fix a couple rows.

    Is there anyway if I log in as the server administrator I can disable this and force the database to allow me to enter this custom value?

    I know what I need to do in this case, but the server won't allow me to make the change.

    Monday, November 25, 2013 12:37 AM

Answers

All replies

  • Turn off auto incrementing feature like in the below link.

    http://stackoverflow.com/questions/1049625/sql-how-to-insert-row-without-auto-incrementing-a-id-column


    ESHANI. Please click "Mark As Answer" if a post solves your problem or "Vote As Helpful" if a post has been useful to you


    • Edited by Eshani Rao Monday, November 25, 2013 12:45 AM
    • Marked as answer by Kensino Monday, November 25, 2013 2:47 AM
    Monday, November 25, 2013 12:45 AM
  • You can turn on identity insert. Please refer to this document:

    http://technet.microsoft.com/en-us/library/ms188059.aspx

    • Marked as answer by Kensino Monday, November 25, 2013 2:47 AM
    Monday, November 25, 2013 12:57 AM
  • >>. I have a table that uses an auto increment value for the PRIMARY KEY. <<

    No, you do not have a table at all! By definition, a key is a subset of attributes of the entity modeled in the table. A physical count of the physical insertions into the psychical disk file that holds the table is not an attribute of the entity. 

    Do you also believe that the parking space number in a garage is the key for an automobile? NO! That is absurd! It is the VIN. 

    >> I need to insert a couple rows into this table, but I need to enter a specific value for the PRIMARY KEY. <<

    You do not have a table, or a key! What you are trying to do is park a car in a particular space in a garage. This disaster is a magnetic tape file written in SQL. It is not a database at all. 

    >> Is there anyway if I log in as the server administrator I can disable this and force the database to allow me to enter this custom value? <<

    Do you understand that this is so screwed up that you have to consider this kind of kludge? What should that tell you? You need to throw this out and start over. Do you want to spend time on never ending repair work or do it right? 


    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    Monday, November 25, 2013 1:04 AM
  • Thanks Eshani Rao, and Jingyang Li

    You both answered my question with knowledge and I was able to turn off the identity for the table and repair the corrupted records very quickly and now everything is up and running correctly again.

    I have also been able to locate the bug in the client program so that this problem doesn't happen again.

    Thanks.

    Monday, November 25, 2013 2:52 AM