MS SQL Server does not let me set IDENTITY_INSERT to ON

I have a table in a database (MS SQL Server 2008) that is filled with data. I want to move some of this data (among which is a column with an IDENTITY constraint) to a table with the same name in another database for back-up purposes. Since I have a table that has an IDENTITY column that I want to move, I need to set IDENTITY_INSERT to ON. I do not want to hard code this, as I may have other tables and columns in the future with this problem.

I have been fiddling with this piece of code for hours and cannot get it to work:

SET IDENTITY_INSERT FDW_test2.dbo.[MIS-ODB_INOUT_LOG] OFF

declare @IDENTITY_INSERTTableCommand nchar(150) =  'SET IDENTITY_INSERT 
FDW_test2.dbo.[MIS-ODB_INOUT_LOG] ON' 

execute (@IDENTITY_INSERTTableCommand) 

INSERT INTO FDW_test2.dbo.[MIS-ODB_INOUT_LOG] ([INOUT_Link ID], 
[INOUT_DIARY_KEY])
     SELECT [INOUT_Link ID], [INOUT_DIARY_KEY]
     FROM   FDW.dbo.[MIS-ODB_INOUT_LOG] 
     WHERE  [INOUT_Client ID] = '1-197'

I get the following error when running this script: An explicit value for the identity column in table can only be specified when a column list is used and IDENTITY_INSERT is ON SQL Server.

It seems that MS SQL is ignoring the 'ON' in my string @IDENTITY_INSERTTableCommand, yet it complains of synthax errors when I enter gibberish into the string.

Can anyone tell me what I am doing wrong? Thank you in advance.

Solution

Once the execute it completed the IDENTITY_INSERT will be back to OFF. It will be ON only inside the execute

Move the Insert statement to string and execute it

declare @IDENTITY_INSERTTableCommand nvarchar(2000) =  
'SET IDENTITY_INSERT FDW_test2.dbo.[MIS-ODB_INOUT_LOG] ON

INSERT INTO FDW_test2.dbo.[MIS-ODB_INOUT_LOG] ([INOUT_Link ID], 
[INOUT_DIARY_KEY])
     SELECT [INOUT_Link ID], [INOUT_DIARY_KEY]
     FROM   FDW.dbo.[MIS-ODB_INOUT_LOG] 
     WHERE  [INOUT_Client ID] = ''1-197''
' 

execute (@IDENTITY_INSERTTableCommand)