SQL Server Performance

identity column problem

Discussion in 'General DBA Questions' started by kidlat, May 29, 2007.

  1. kidlat New Member

    gud day to you. i tried to issue a insert t-sql command to a certain table since the one of the column (objectnotetoken is an identity). i got a "An explicit value for the identity column in table 'objectnotes' can only be specified when a column list is used and IDENTITY_INSERT is ON." error. is there other way to resolve this issue?
  2. deepakontheweb New Member

    BOL Says:<br /><br />SET IDENTITY_INSERT<br />Allows explicit values to be inserted into the identity column of a table.<br /><br />Syntax<br />SET IDENTITY_INSERT [ database. [ owner. ] ] { table } { ON | OFF }<br /><br /><br />Example;<br /><br />-- Attempt to insert an explicit ID value of 3;<br />-- should return a warning.<br />INSERT INTO products (id, product) VALUES(3, 'garden shovel')<br />GO<br />-- SET IDENTITY_INSERT to ON.<br />SET IDENTITY_INSERT products ON<br />GO<br /><br />-- Attempt to insert an explicit ID value of 3<br />INSERT INTO products (id, product) VALUES(3, 'garden shovel').<br />GO<br /><br /><br />I like SQL BOL a lot <img src='/community/emoticons/emotion-5.gif' alt=';)' /><br /><br /><b>Deepak Kumar</b><br /><font size="1">SQL Server MVP, MCDBA<br />deepak.kumar@sqlknowledge.com<br /><br />Disclaimer: This post is provided as is with no rights & warranty for accuracy, for the sake of knowledge sharing only.</font id="size1">
  3. Madhivanan Moderator

    Also, why do you want to insert value to identity column when values are automatically generated whenever data are added? In your insert statement remove the identity column

    Madhivanan

    Failing to plan is Planning to fail
  4. FrankKalis Moderator

    <blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by kidlat</i><br /><br />gud day to you. i tried to issue a insert t-sql command to a certain table since the one of the column (objectnotetoken is an identity). i got a "An explicit value for the identity column in table 'objectnotes' can only be specified when a column list is used and IDENTITY_INSERT is ON." error. is there other way to resolve this issue?<br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br />There is an easy to resolve this issue: Just don't provide any value for this column in your INSERT statement. [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br />--<br />Frank Kalis<br />Microsoft SQL Server MVP<br />Contributing Editor, Writer & Forum Moderator<a target="_blank" href=http://www.sql-server-performance.com>http://www.sql-server-performance.com</a><br />Webmaster:<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a>

Share This Page