如何获取由插入产生的标识字段? [英] How do I get the Identity field that results from an insert?

问题描述

我将像这样将数据插入到表中:

I'm going to insert data into a table like so:

Insert Into MyTable (Field1, Field2)
           Values   ('test', 5)

当插入发生时，新行将在 ID 列中有一个标识值.如果我的 T-SQL 代码中有一个名为 @ID 的变量，我如何用 T-SQL 插入的结果填充 @ID?

When that insert occurs, the new row is going to have an identify value in the ID column. If have a variable called @ID in my T-SQL code, how do I populate @ID with the result of the T-SQL Insert?

Declare @ID int

Insert into MyTable (Field1, Field2)
             Values ('test', 5)

--//How do I populate @ID with the value of ID of the record that was just inserted?

推荐答案

有两种方法 - 第一种是使用 SCOPE_IDENTITY:

There are two ways - the first is to use SCOPE_IDENTITY:

DECLARE @ID INT

INSERT INTO MyTable (Field1, Field2)
VALUES ('test', 5)

SELECT @ID = SCOPE_IDENTITY() 

不要使用@@IDENTITY -- 它包含的值是针对最后更新的 IDENTITY 列，而不考虑范围.意思是，它保存您的 INSERT 的值是不可靠的，但 SCOPE_IDENTITY 确实如此.

Don't use @@IDENTITY -- the value it contains is for the last updated IDENTITY column without regard for scope. Meaning, it's not reliable that it holds the value for your INSERT, but SCOPE_IDENTITY does.

另一种选择是使用 OUTPUT子句(SQL Server 2005+).

The other alternative is to use the OUTPUT clause (SQL Server 2005+).

这篇关于如何获取由插入产生的标识字段?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！