如何在作为变量传递的表名上设置 identity_insert [英] How can I set identity_insert on a tablename passed as a variable

问题描述

我在 @strDBName 中有数据库名称.我构建了 SET 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.

  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)

推荐答案

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

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)

给定:

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)

这篇关于如何在作为变量传递的表名上设置 identity_insert的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！