How can I set identity_insert to tablename passed as variable

Question

I have a database name in @strDBName. I create SET IDENTITY_INSERTand execute it. There is no error, but subsequent insertion fails. The code below illustrates the problem.

  Declare @Query Varchar(MAX)
  SET @Query = 'SET IDENTITY_INSERT '+ @strDBName +'..TableName ON'
  EXEC(@Query)


  INSERT INTO [TableName] ... (MAX) Value from another table and other applicable record.


  SET @Query = 'SET IDENTITY_INSERT '+ @strDBName +'..TableName OFF'
  EXEC(@Query)

Answer 1

, , MVCE . Kris, , , SqlInjection ( , sql sp_executesql)

:

CREATE TABLE TableName
(
    ID INT IDENTITY(1,1)
);

:

DECLARE @strDBName VARCHAR(100) = 'MyDatabase';
Declare @Query Varchar(MAX)
SET @Query = 'SET IDENTITY_INSERT '+ @strDBName +'..TableName ON; '
SET @Query = @Query + 'INSERT INTO '+ @strDBName 
 +'..[TableName](ID) SELECT COALESCE(MAX(ID), 0)+1 FROM '+ @strDBName +'..TableName; '
SET @Query = @Query + 'SET IDENTITY_INSERT '+ @strDBName +'..TableName OFF;'
EXEC(@Query)