How to turn IDENTITY INSERT on and off using SQL Server 2008

Question

Why am I getting an error doing an insert when IDENTITY INSERT is set to OFF     How do I turn it on properly in SQL Server 2008  Is it by using SQL Server Management Studio   I have run this query   SET IDENTITY INSERT Database  dbo  Baskets ON   Then I got the message back in the console that the Command s  completed successfully  However when I run the application  it still gives me the error shown below   Cannot insert explicit value for identity column in table  Baskets  when  IDENTITY INSERT is set to OFF

User · Answer

Another option is where you have tables like  type  or  status   for example  OrderStatus  where you always want to control the Id value  create the Id  Primary Key  column without it being an Identity column is the first place

User · Answer

Import  You must write columns in INSERT statement  INSERT INTO TABLE SELECT   FROM       Is not correct   Insert into Table Field1      Select  Field1      from TABLE   Is correct

User · Answer

You need to add the command  go  after you set the identity insert  Example   SET IDENTITY INSERT sometableWithIdentity ON go  INSERT sometableWithIdentity  IdentityColumn  col2  col3       VALUES  AnIdentityValue  col2value  col3value        SET IDENTITY INSERT sometableWithIdentity OFF go

User · Answer

It looks necessary to put a SET IDENTITY INSERT Database dbo Baskets ON  before every SQL INSERT sending batch   You can send several INSERT     VALUES     commands started with one SET IDENTITY INSERT     ON  string at the beginning  Just don t put any batch separator between   I don t know why the SET IDENTITY INSERT     ON stops working after the sending block  for ex    ExecuteNonQuery   in C     I had to put SET IDENTITY INSERT     ON  again at the beginning of next SQL command string

User · Answer

I had a problem where it did not allow me to insert it even after setting the IDENTITY INSERT ON   The problem was that i did not specify the column names and for some reason it did not like it    INSERT INTO tbl Values vals    So basically do the full INSERT INTO tbl cols  Values vals

User · Answer

Via SQL as per MSDN  SET IDENTITY INSERT sometableWithIdentity ON  INSERT INTO sometableWithIdentity       IdentityColumn  col2  col3       VALUES       AnIdentityValue  col2value  col3value        SET IDENTITY INSERT sometableWithIdentity OFF   The complete error message tells you exactly what is wrong        Cannot insert explicit value for identity column in table  sometableWithIdentity  when IDENTITY INSERT is set to OFF

User · Answer

I know this is an older thread but I just bumped into this  If the user is trying to run inserts on the Identity column after some other session Set IDENTITY INSERT ON  then he is bound to get the above error    Setting the Identity Insert value and the subsequent Insert DML commands are to be run by the same session   Here  Beginner was setting Identity Insert ON separately and then running the inserts from his application  That is why he got the below Error   Cannot insert explicit value for identity column in table  Baskets  when  IDENTITY INSERT is set to OFF

User · Answer

This is likely when you have a PRIMARY KEY field and you are inserting a value that is duplicating or you have the INSERT IDENTITY flag set to on

[sql] How to turn IDENTITY_INSERT on and off using SQL Server 2008?

Examples related to sql

Examples related to sql-server-2008