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?
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">
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
<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>