如何使用 SCOPE_IDENTITY 检索最后插入的 ID [英] How to use SCOPE_IDENTITY to retrieve the last ID that was inserted
问题描述
假设我有两张桌子.第一个表的主键是另一个表的外键.
Suppose I have two table. First table's primary key is the foreign key for another table.
表Member
在Member_detail
中有它的主键作为外键.
Table Member
has its primary key as the foreign key in Member_detail
.
因此,当我使用存储过程在 Member
表中插入一行时,我需要获取要添加到 Member_detail
表的主键值.
So when I insert a row in Member
table using a Stored Procedure, I need to get the primary key value to add to the Member_detail
table.
我使用的一种方法是:
SELECT Max(MemberID)
FROM Member
然后将此 Memberid
传递到我的 Member_detail
表,但在 以下帖子,我读到不推荐使用 Max
函数,我应该使用 SCOPE_IDENTITY
,但我不知道如何使用它.
Then passing this Memberid
to my Member_detail
table, but on the following post, I read that the Max
function is not recommended and that I should use SCOPE_IDENTITY
, but I don't know how to use of it.
谁能给我举个例子?
推荐答案
SCOPE_IDENTITY
返回插入到同一范围内的标识列中的最后一个标识值.
SCOPE_IDENTITY
returns the last identity value inserted into an identity column in the same scope.
假设您有 2 张桌子:
Given you have 2 tables:
Member: id int (primaryKey), name varchar
Member_Detail: id int (primaryKey), pk int (foreignKey), name varchar
你可以这样做:
DECLARE @MemberId int
INSERT INTO Member (name) VALUES ('hello');
SET @MemberId = SCOPE_IDENTITY()
INSERT INTO Member_Detail (pk, name) VALUES (@MemberId, 'hello again')
MSDN 参考:
返回插入到同一范围内的标识列中的最后一个标识值.作用域是一个模块:存储过程、触发器、函数或批处理.因此,如果两个语句在同一个存储过程、函数或批处理中,则它们在同一范围内.
Returns the last identity value inserted into an identity column in the same scope. A scope is a module: a stored procedure, trigger, function, or batch. Therefore, two statements are in the same scope if they are in the same stored procedure, function, or batch.
这篇关于如何使用 SCOPE_IDENTITY 检索最后插入的 ID的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!