How to use SCOPE_IDENTITY to retrieve the last ID that was inserted
SCOPE_IDENTITY
returns the last identity value inserted into an identity column in the same scope.
Given you have 2 tables:
Member: id int (primaryKey), name varchar
Member_Detail: id int (primaryKey), pk int (foreignKey), name varchar
You can do this:
DECLARE @MemberId int
INSERT INTO Member (name) VALUES ('hello');
SET @MemberId = SCOPE_IDENTITY()
INSERT INTO Member_Detail (pk, name) VALUES (@MemberId, 'hello again')
MSDN Reference:
SCOPE_IDENTITY (Transact-SQL)
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() doesn't work properly ? How to get the identity of the last row inserted by a stored procedure?
Another alternative to using @@IDENTITY or SCOPE_IDENTITY is something like this -
create table Fred (
Id int identity(1, 1)
, SomeText varchar(120)
, SomeDate datetime
, SomeAmt float
);
declare @t table (Id int);
insert into Fred (SomeText, SomeDate, SomeAmt)
output inserted.Id into @t
select 'BILL', GETDATE(), 123.4;
select * from @t;
At the end you will find that the table variable @t will have one row with the newly inserted row's ID in it.
What is the difference between Scope_Identity(), Identity(), @@Identity, and Ident_Current()?
- The
@@identity
function returns the last identity created in the same session. - The
scope_identity()
function returns the last identity created in the same session and the same scope. - The
ident_current(name)
returns the last identity created for a specific table or view in any session. - The
identity()
function is not used to get an identity, it's used to create an identity in aselect...into
query.
The session is the database connection. The scope is the current query or the current stored procedure.
A situation where the scope_identity()
and the @@identity
functions differ, is if you have a trigger on the table. If you have a query that inserts a record, causing the trigger to insert another record somewhere, the scope_identity()
function will return the identity created by the query, while the @@identity
function will return the identity created by the trigger.
So, normally you would use the scope_identity()
function.
If I use scope_identity() ; is it possible that it can return the value of a wrong row if several users are using the application at the same time
Using OUTPUT
statement you can do it in one query or create procedure for this
INSERT INTO survey (id, title, detail, employerid, userid)
OUTPUT @title, @detail, inserted.ID INTO surveyquestioncategory (title, detail, surveyid)
VALUES (@id, @title, @detail, @eid, @uid);
From MSDN OUTPUT Clause (Transact-SQL)
Using @@identity or output when inserting into SQL Server view?
Avoid using @@IDENTITY or SCOPE_IDENTITY() if your system is using Parallel plans as there is a nasty bug. Please refer -
http://connect.microsoft.com/SQL/feedback/ViewFeedback.aspx?FeedbackID=328811
Better way to fetch the inserted Identity ID would be to use OUTPUT clause.
CREATE TABLE tblTest
(
Sno INT IDENTITY(1,1) NOT NULL,
FirstName VARCHAR(20)
)
DECLARE @pk TABLE (ID INT)
INSERT INTO tblTest(FirstName)
OUTPUT INSERTED.Sno INTO @pk
SELECT 'sample'
SELECT * FROM @pk
EDIT:
It would work with Views as well. Please see the sample below. Hope this is what you were looking for.
CREATE VIEW v1
AS
SELECT sno, firstname FROM tbltest
GO
DECLARE @pk TABLE (ID INT)
INSERT INTO v1(FirstName)
OUTPUT INSERTED.Sno INTO @pk
SELECT 'sample'
SELECT ID FROM @pk
scope_identity vs ident_current
In that case you need to write the table name, what happens if you decide to change the table name? You then also must not forget to update your code to reflect that. I always use SCOPE_IDENTITY unless I need the ID from the insert that happens in a trigger then I will use @@IDENTITY
Also the bigger difference is that IDENT_CURRENT will give you the identity from another process that did the insert (in other words last generated identity value from any user)
so if you do an insert and then someone does an insert before you do a SELECT IDENT_CURRENT you will get that other person's identity value
See also 6 Different Ways To Get The Current Identity Value which has some code explaining what happens when you put triggers on the table
SQL Server - Best way to get identity of inserted row?
@@IDENTITY
returns the last identity value generated for any table in the current session, across all scopes. You need to be careful here, since it's across scopes. You could get a value from a trigger, instead of your current statement.SCOPE_IDENTITY()
returns the last identity value generated for any table in the current session and the current scope. Generally what you want to use.IDENT_CURRENT('tableName')
returns the last identity value generated for a specific table in any session and any scope. This lets you specify which table you want the value from, in case the two above aren't quite what you need (very rare). Also, as @Guy Starbuck mentioned, "You could use this if you want to get the current IDENTITY value for a table that you have not inserted a record into."The
OUTPUT
clause of theINSERT
statement will let you access every row that was inserted via that statement. Since it's scoped to the specific statement, it's more straightforward than the other functions above. However, it's a little more verbose (you'll need to insert into a table variable/temp table and then query that) and it gives results even in an error scenario where the statement is rolled back. That said, if your query uses a parallel execution plan, this is the only guaranteed method for getting the identity (short of turning off parallelism). However, it is executed before triggers and cannot be used to return trigger-generated values.
Related Topics
How to Run Native SQL with Entity Framework
Seeing the Underlying SQL in the Spring Jdbctemplate
How Would You Implement Sequences in Microsoft SQL Server
Transpose Rows into Columns in Bigquery (Pivot Implementation)
Case When Statement for Order by Clause
Dynamic SQL to Generate Column Names
Create Trigger to Log SQL That Affected Table
Why Does This SQL Code Give Error 1066 (Not Unique Table/Alias: 'User')
Does Sparksql Support Subquery
How to Run a Stored Procedure Every Day in SQL Server Express Edition
Generate All Combinations in SQL
Is There a Performance Difference Between Cte , Sub-Query, Temporary Table or Table Variable