获取新的SQL记录ID

Question

我刚刚插入一条新记录，如何获取自动生成的ID？（使用ASP classic和MSSQL 2005）

- Keith

---

感谢所有建议使用SELECT SCOPE_IDENTITY()的人。我能够创建一个存储过程： SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER PROCEDURE [dbo].[spIns] (@N varchar(30) AS BEGIN TRANSACTION Ins INSERT INTO A (n) VALUES (@N) SELECT NewID = SCOPE_IDENTITY() - Keith

使用VB我调用了存储过程：<br> Set cn = Server.CreateObject("ADODB.Connection") connectString = "DSN" cn.Open connectString, "user", "PW0rd" Set rs = Server.CreateObject("ADODB.Recordset") set rs = cn.Execute("EXEC [DB].[dbo].[A] @Str=" & str)<br> '返回值：<br> resultID = rs(0) - Keith

Answer 1

SELECT SCOPE_IDENTITY() 

使用@@IDENTITY可能会产生意想不到的结果，因此在使用该函数时要小心。触发器插入其他表中的记录将导致@@IDENTITY值改变，而SCOPE_IDENTITY()函数将只返回当前作用域中最后一个标识符。以下示例演示了@@IDENTITY和SCOPE_INSERT()之间的区别以及它们可能返回不同的值。

use tempdb
go
create table table1
    (ID int identity)
go
create table table2
    (ID int identity(100, 1))
go
create trigger temptrig 
    on table1 
    for insert
as
begin

    insert  table2 
     default values;

end
go
insert  table1 
default values;
select  SCOPE_IDENTITY(), 
        @@IDENTITY

这里没有被讨论的另一个选项是使用SQL 2005中的OUTPUT子句。在这种情况下，您只需将输出子句添加到插入语句中，然后从代码中捕获该记录集。当插入多条记录而不仅仅是一条记录时，此方法非常有效。

use tempdb
go
create table table1
    (ID int identity)
go
insert   table1 
output   inserted.ID
default values;
--OR...
insert   table1 
output   inserted.$identity
default values;

- Scott

Answer 2

在SQL中获取最后一个标识符有三种方法。

其他人已经提到了它们，但为了完整起见：

• @@IDENTITY - 还可以返回在同一范围内（例如触发器）创建的对象的ID
• IDENT_CURRENT - 仅限于一个表，但不限于您的范围，因此对于繁忙的表可能会产生错误的结果
• Scope_Identity() - 仅限于请求的范围。99%的情况下使用此方法

此外，有三种方法将该ID返回给客户端代码：

• Use an output parameter in a stored procedure

  INSERT INTO [MyTable] ([col1],[col2],[col3]) VALUES (1,2,3); 
  SELECT @OutputParameterName = Scope_Identity();
  

• Use a return value.

  INSERT INTO [MyTable] ([col1],[col2],[col3]) VALUES (1,2,3); 
  Return Scope_Identity();
  

• Select the id into a result set. For example, your sql statement would look something like this:

  Dim ResultID As Integer
  Dim strSQL As String
  strSQL = "INSERT INTO [MyTable] ([col1],[col2],[col3]) VALUES (1,2,3); SELECT Scope_Identity();"
  rsResults.Open strSQL, oConn
  ResultID = rsResults("ID")
  

不幸的是（或者从我的角度来看是幸运的），我的经典ASP已经过时，无法展示客户端代码的前两个例子。

- Joel Coehoorn

Answer 3

SELECT @@IDENTITY通常可行，但可能返回由触发器或其他原因插入的记录的标识，而不是原始记录的标识。

我建议使用SELECT SCOPE_IDENTITY。它仅返回当前范围内插入的值。

还有一个"IDENT_CURRENT（tablename）"，它返回特定表中插入的最后一个标识。

- BradC

Answer 4

选择SELECT @@Identity或SELECT SCOPE_IDENTITY()都可以，但选择SCOPE_IDENTITY()更安全，因为它返回当前范围内最后生成的自动增量ID。例如，假设我们有一个名为ScopeIDTable的表，在该表上有一个触发器。此触发器将在TriggerIdTable中插入一条记录，两个表都有一个自动递增列。

如果您使用SELECT @@Identity，则会获得该会话中的最后一个自动递增ID，这将是从触发器（TriggerIdTable）中生成的ID。

如果您使用SELECT SCOPE_IDENTITY()，则会获得来自ScopeIdTable的ID。

- Josh

Answer 5

你执行了查询

select scope_identity()

在执行任何其他操作之前，使用相同的数据库连接。结果是，您可能期望的记录集包含一个具有单个字段的行。您可以使用索引0访问该字段，或者如果您更喜欢，可以为其命名：

select scope_identity() as lastId

- Guffa

Answer 6

我一直想知道为什么有人会想要使用

@@identity

自从

select scope_identity() 

显然这是实现Scot所要求的最安全的方法。

- Roel Snetselaar

Answer 7

感谢所有建议使用SELECT SCOPE_IDENTITY()的人。我已经成功创建了一个存储过程：

USE [dbname]
GO 
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[spInsert]
(
   @Nn varchar(30)
)
AS
BEGIN TRANSACTION InsertRecord 
   INSERT INTO A (Nn) 
   VALUES (@Nn) 
   SELECT NewID = SCOPE_IDENTITY()   -- returns the new record ID of this transaction
   COMMIT TRANSACTION InsertRecord

并使用VB调用sproc：

Dim strNn '<- var to be passed'
Set cn = Server.CreateObject("ADODB.Connection") 
connectString = "DSN" 
cn.Open connectString, "user", "PW0rd" 
Set rs = Server.CreateObject("ADODB.Recordset") 
set rs = cn.Execute("EXEC [dbname].[dbo].[A] @Nn=" & strNn)
'return the value'
resultID = rs(0)

现在，每当我引用新创建的ID时，我都可以使用resultID。

- Keith

Answer 8

当需要一次性以集合方式插入多个记录时，它可能会变得更加有趣。

我有时会使用客户端生成的GUID（但对于经典ASP，您可能需要使用实用程序来生成值），或者更常见的是在服务器端的GUID键列上使用NEWSQUENTIALID()约束。

我知道并不是每个人都喜欢GUIDS，因为有一些相当有效的原因（例如它们的大小以及它如何影响索引/分页）。

http://www.sqlmag.com/Articles/Index.cfm?ArticleID=50164&pg=2

- kpollock