仅向具有一个 IDENTITY 列的表中插入行

我有一个只有一列的表 Administrator，adminId 是主键。因为商业规则，必须这样做。

我希望一劳永逸地了解如何编写在表中插入值的存储过程，如下所示。我正在使用 SQL Server 和 T-SQL，并尝试使用 SCOPE _ IDENTITY () ，但这不起作用，因为该表的 INSERT _ IDENTITY 为 false 或 off。

我不希望仅仅为了能够插入新行而插入虚拟值。谢谢！

69098 次浏览
小开

您需要将 IDENTITY _ INSERT 添加到 select 语句:

SET IDENTITY_INSERT MyTable ON


INSERT INTO MyTable
(AdminCol)


SELECT AdminColValue


FROM Tableb

当你完成后，一定要记得这么做

SET IDENTITY_INSERT MyTable OFF

下面是从 BOL: http://msdn.microsoft.com/en-us/library/aa259221(SQL.80).aspx中对它如何工作的一个很好的描述

小开
最佳答案

如果有一个列是 IDENTITY，只需执行下面的操作

INSERT MyTable DEFAULT VALUES;  --allows no column list. The default will be the IDENTITY
SELECT SCOPE_IDENTITY();

如果你没有标识符，那么你可以设置它吗? 这是最好的方法... 并使用上面的 SQL。

如果不是，则要插入新行

INSERT MyTable (admidid)
OUTPUT INSERTED.admidid --returns result to caller
SELECT ISNULL(MAX(admidid), 0) + 1 FROM MyTable

备注:

  • 在高负荷下，MAX 解决方案可能会重复失败
  • SCOPE _ IDENTITY 是 之后事实上，不是之前
  • SCOPE _ IDENTITY 只适用于 IDENTITY 列
  • Output 子句替换 MAX 解决方案的 SCOPE _ IDENTITY
小开

@ Phil: 你的意思是你的表有两列，自动递增的 PK 列和一个 AdminName 列？如果它只有 AdminName 所在的一列，那么 AdminName 就是 PK，当然，您不能自动递增字符串。业务规则是否期望您将完全限定的 Windows 用户名作为主键？这将是可行的并且有意义的，因为这样您就不需要 AdminName 列上的替代唯一索引。

但如果您的表有两列，而不是一列:

在 SQLServer 中，自动递增是表/列定义的一部分。将该列定义为一个整数，然后将其设置为 标识列，指定增量，通常是1，但也可以是2、5或10或其他值。要插入一行，只需插入其他列的值，不对 PK 列进行任何操作:

insert into T
(foo)   -- column(s) list
values('bar') -- values list

执行插入操作的存储过程可以使 SCOPE _ IDENTITY 成为 RETURN 值，或者 SCOPE _ IDENTITY 可以作为 OUT 参数传递回客户端。

P.S. SCOPE _ IDENTITY ()返回当前作用域中最近生成的自增量标识值; 它不生成下一个标识值。

编辑:

假设，管理员表包含一组管理员。但是，如果它除了整数主键列之外没有其他任何列，那么就没有办法识别管理员; 您唯一能做的就是将它们彼此区分开来。那根本不能让你走多远。但是，如果管理员表具有下列结构之一:

ID   INTEGER PRIMARY KEY AUTOINCREMENT
windowsusername   varchar(50)  (unique index)

或者

windowsusername varchar(50) primary key

您将能够从其他表中引用 Administrator 的表，并且外键将是 MEANINGFUL。这正是由单个整数列组成的表所缺乏的含义。

如果有两列，那么可以让存储过程执行以下操作:

insert into Administrators
(windowsusername)
values('mydomain\someusername');
return SCOPE_IDENTITY();

你的客户端程序会返回一个返回值，即自动生成并分配给新插入的行的自动递增的 id。这种方法是通常的做法，我甚至可以说它被认为是“最佳做法”。

附言。你提到你不知道如何“插入一个值”，如果你“没有任何东西可以插入”。这里有一个矛盾。如果没有要插入的内容，为什么要插入？如果您对客户一无所知，为什么要创建一个新的客户记录呢？不是他们的名字，他们的城市，他们的电话号码，什么都没有？


提交答案