How to use SCOPE_IDENTITY to retrieve the last identifier that was inserted

Question

Suppose I have two tables. The first key of the first table is the foreign key for another table.

The Member table has its primary key as a foreign key in Member_detail .

Therefore, when I insert a row into the Member table using a stored procedure, I need to get the primary key value to add to the Member_detail table.

One way to use:

 SELECT Max(MemberID) FROM Member 

Then, passing this Memberid to my Member_detail table, but in the next 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 it.

Can someone give me an example?

Answer 1

SCOPE_IDENTITY returns the last identification value inserted in the identifier column in the same scope.

Given that you have 2 tables:

 Member: id int (primaryKey), name varchar Member_Detail: id int (primaryKey), pk int (foreignKey), name varchar 

You can do it:

 DECLARE @MemberId int INSERT INTO Member (name) VALUES ('hello'); SET @MemberId = SCOPE_IDENTITY() INSERT INTO Member_Detail (pk, name) VALUES (@MemberId, 'hello again') 

MSDN Link:

SCOPE_IDENTITY (Transact-SQL)

Returns the last authentication value inserted in the identifier column in the same scope. Volume is a module: stored procedure, trigger, function, or lot. Therefore, two operators are in the same area if they are in the same stored procedure, function, or batch.

Answer 2

I saw "funny" behavior with scope_identity. As a result, I like to use the output clause. Here is an example:

 declare @id table (i int) insert into Member (name) values ('NightKnight') output (MemberId) into @id select * from @id