Set IDENTITY INSERT ON is not working

Question

I want to copy a table Equipment from one database MyDBQA to our test database MyDB  There is an identity column in the table which is the primary key  int  not null    But I got an error      Msg 8101  Level 16  State 1  Line 2   An explicit value for the identity column in table  MyDB dbo Equipment  can only be specified when a column list is used   and IDENTITY INSERT is ON    My script   SET IDENTITY INSERT  MyDB   dbo   Equipment  ON INSERT INTO  MyDB   dbo   Equipment   SELECT   FROM  MyDBQA   dbo   Equipment  SET IDENTITY INSERT  MyDB   dbo   Equipment  OFF

User · Answer

The relevant part of the error message is

...when a column list is used...

You are not using a column list, you are using SELECT *. Use a column list instead:

SET IDENTITY_INSERT [MyDB].[dbo].[Equipment] ON 

INSERT INTO [MyDB].[dbo].[Equipment] (Col1, Col2, ...)
SELECT Col1, Col2, ... FROM [MyDBQA].[dbo].[Equipment] 

SET IDENTITY_INSERT [MyDB].[dbo].[Equipment] OFF

User · Answer

Here s Microsoft s write up on using SET IDENTITY INSERT  which might be helpful to others seeing this post if they  like me  found this post when trying to recreate deleted records while maintaining the original identity column value   to recreate deleted records with original identity column value  http   msdn microsoft com en-us library aa259221 v sql 80  aspx

User · Answer

In VB code  when trying to submit an INSERT query  you must submit a double query in the same  executenonquery  like this   sqlQuery    SET IDENTITY INSERT dbo TheTable ON  INSERT INTO dbo TheTable  Col1  COl2  VALUES  Val1  Val2   SET IDENTITY INSERT dbo TheTable OFF     I used a   separator instead of a GO   Works for me  Late but efficient

User · Answer

You might be just missing the column list  as the message says  SET IDENTITY INSERT  MyDB   dbo   Equipment  ON  INSERT INTO  MyDB   dbo   Equipment               COL1               COL2  SELECT COL1         COL2 FROM    MyDBQA   dbo   Equipment   SET IDENTITY INSERT  MyDB   dbo   Equipment  OFF

[sql-server] Set IDENTITY_INSERT ON is not working

Examples related to sql-server

Examples related to sql-server-2008

Examples related to tsql