SQL Server - How to set IDENTITY_INSERT =On

Asked By Masuma Aktar on 15-Jan-09 09:09 AM
Hello all,
To make automatic incrementation of my Employee table,EmployeeId  set as primary key and Identity column. But when i insert any new value i am getting this error:"Cannot insert explicit value for identity column in table 'Employee' when IDENTITY_INSERT is set to OFF." How to set IDENTITY_INSERT =On.

Thanks,
Masum


Dont insert any value for the identity column. It will be generated

[)ia6l0 iii replied to Masuma Aktar on 15-Jan-09 09:22 AM
remove that column from your insert script

This error occurs when attempting

Venkat K replied to Masuma Aktar on 15-Jan-09 10:03 AM

to insert a row containing a specific identity value into a table that contains an identity column. Run following commands according to your SQL Statement:

Before your SQL Statement:

SET IDENTITY_INSERT <tablename> ON
            

{YOUR SQL INSERT STATEMENT}

After your SQL Statement:

SET IDENTITY_INSERT <tablename> OFF

reply example

alice johnson replied to Masuma Aktar on 15-Jan-09 10:08 AM
SET IDENTITY_INSERT [ database_name . [ schema_name ] . ] table { ON |
OFF }
read this
C_A P replied to Masuma Aktar on 16-Jan-09 03:04 AM
When you create a new SQL Server database (including SQL Server 2000 Desktop Engine), the IDENTITY_INSERT options are set to OFF.

If you upsize a Microsoft Access table that contains a field of the AutoNumber data type, the field is created on SQL Server as an IDENTITY column. Unlike the AutoNumber data type, you cannot directly edit IDENTITY columns, nor explicitly insert data into an IDENTITY column while the IDENTITY_INSERT option for that table is set to OFF. To insert or update data in an IDENTITY column, you must set the IDENTITY_INSERT option to ON.
http://support.microsoft.com/kb/294923#top

RESOLUTION
You can use the SET IDENTITY_INSERT statement to set the IDENTITY_INSERT option...

loadTOCNode(1, 'resolution'); You can use the SET IDENTITY_INSERT statement to set the IDENTITY_INSERT option. SET IDENTITY_INSERT always references a table, and you should place it before the UPDATE or INSERT statement that modifies or inserts data into an IDENTITY column. The following example sets IDENTITY_INSERT for the NewEmployees table. 
   SET IDENTITY_INSERT NewEmployees ON
TRY THIS LINK
C_A P replied to Masuma Aktar on 16-Jan-09 03:04 AM

http://www.raritanval.edu/departments/cis/full-time/Schwarz/sql/lesson7.htm

http://www.bigresource.com/MS_SQL--Restarting-the-identity-column-on-table-variable--v9gNjNrb.html

http://www.4guysfromrolla.com/ASPScripts/PrintPage.asp?REF=%2Fwebtech%2F041906-1.shtml