sql - 如何在作为变量传递的表名上设置identity_insert

Question

我的数据库名称位于 @strDBName 中。我构建并执行SET IDENTITY_INSERT。没有错误，但后续插入失败。下面的代码说明了这个问题。

  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

为了支持 Brad 在评论中给出的答案，这里有一个在单个动态查询中执行整个插入序列的 MVCE。根据 Kris 的评论，请确保将数据库名称列入白名单，因为查询容易受到 SqlInjection 的攻击(不幸的是，数据库名称无法通过 sp_executesql 在动态 sql 中参数化)

给定:

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)