SQL SERVER IDENTITY COLUMN
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 2 of 2

Thread: SQL SERVER IDENTITY COLUMN

  1. #1
    Join Date
    Feb 2001
    Posts
    17
    hi,

    i am using sql server 7.0 when trying a row on a identity column i am getting the following error

    Microsoft][ODBC SQL Server Driver][SQL Server]Cannot insert explicit value for identity column in table 'TenderDetails' when IDENTITY_INSERT is set to OFF.

    How do I make identity_insert to ON?
    I tried giving at procedure, but it did not work.
    can anyone help me out

  2. #2
    Join Date
    Nov 2000
    Location
    Baltimore, MD USA
    Posts
    1,339
    Uh, considering the default is OFF and you are actually using a sequence, the question is why do you want to turn it on.

    Actually, I have a guess. You are used to Oracle where you always have to insert the value. In SQLServer, you define a column as an IDENTITY column. That means the you never have to insert into that column. SQLServer will not only come up with the next value for you, it will also handle inserting it for you. That column should never be listed in your INSERT statement. However, this also means that you *must* list the columns that you do explicitely want to insert into - no more implicit column list in the insert statement. Of course, it is a very bad practice to use the implicit lists anyway.

    ie
    INSERT INTO T1
    SELECT * FROM T2
    ...is bad, and not viable when using sequences

    INSERT INTO T1
    (COL2, COL3)
    SELECT
    COL2,COL3
    FROM T2

    ...must be used instead.

    Hope this makes sense. Let me know if I missed the mark or you have any more questions.

    - Chris

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width