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

I have the Database name in a @strDBName. I build the SET IDENTITY_INSERT and execute it. There's no error, but a subsequent insert fails. The code below illustrates the problem.

我在@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)

1 个解决方案

#1

Just to backup Brad's answer given in the comments, here's an MVCE of doing the entire insertion sequence in a single dynamic query. As per Kris' comment, ensure that the database name is white listed, as the query is vulnerable to SqlInjection (unfortunately, database names cannot be parameterized in dynamic sql via sp_executesql)

只是为了备份Brad在评论中给出的答案，这里是一个在单个动态查询中完成整个插入序列的MVCE。根据Kris的评论，确保数据库名称是白名单，因为查询容易受到SqlInjection的攻击（遗憾的是，数据库名称无法通过sp_executesql在动态sql中参数化）

Given:

鉴于：

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

A single batch can be executed:

可以执行单个批次：

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)