Set IDENTITY_INSERT ON is not working

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

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 

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 

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!

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