Set IDENTITY_INSERT ON is not working
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!