0

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.

Vraagteken
  • 43
  • 3

1 Answers1

1

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)
Pரதீப்
  • 91,748
  • 19
  • 131
  • 172
  • Thank you so much! This worked. :D I'm so glad I didn't spend my afternoon for nothing. – Vraagteken Jun 21 '17 at 14:55
  • To anyone reading this in 2019: if you do not _have_ to run your SQL as a string (e.g. you are only specifying parameters for the where clause), you can simply execute the IDENTITY_INSERT statement as a normal statement in the SQL.Try to avoid dynamic SQL. – kaesaecracker Dec 11 '19 at 10:34