SELECT @@IDENTITY
It returns the last IDENTITY value produced on a connection, regardless of the table that produced the value, and regardless of the scope of the statement that produced the value.
@@IDENTITY will return the last identity value entered into a table in your current session. While @@IDENTITY is limited to the current session, it is not limited to the current scope. If you have a trigger on a table that causes an identity to be created in another table, you will get the identity that was created last, even if it was the trigger that created it.
SELECT SCOPE_IDENTITY()
It returns the last IDENTITY value produced on a connection and by a statement in the same scope, regardless of the table that produced the value.
SCOPE_IDENTITY(), like @@IDENTITY, will return the last identity value created in the current session, but it will also limit it to your current scope as well. In other words, it will return the last identity value that you explicitly created, rather than any identity that was created by a trigger or a user defined function.
SELECT IDENT_CURRENT(‘tablename’)
It returns the last IDENTITY value produced in a table, regardless of the connection that created the value, and regardless of the scope of the statement that produced the value.
IDENT_CURRENT is not limited by scope and session; it is limited to a specified table. IDENT_CURRENT returns the identity value generated for a specific table in any session and any scope.
To avoid the potential problems associated with adding a trigger later on, always use SCOPE_IDENTITY() to return the identity of the recently added row in your T SQL Statement or Stored Procedure.
Reference : Pinal Dave (https://blog.sqlauthority.com)
230 Comments. Leave new
Very Nice Pinal
Alok Kumar Sharma
Hi Dave,
i m new to SQL server, i m stuck in the problem, i have to find max id based on department
eg:
e_id e_dept
as123mk MKT
as124mk MKT
as125it IT
as126it IT
i m having 2 column e_id and e_dept which store employee id and employee department
in above table i have to find max id in MKT dept that is 124 and max id in IT dept that is 126.
Thanks in advance
waiting for reply
Waht is the use of the @@IDENTITY in sql
Hi
I AM UDAY
I AM INSERTING BULK DATA USING XML IN SQL SERVER 2005.i AM USING C#.NET.
fOR SINGLE TABLE IT WORKS NICE BUT MY PROBLEM IS.I AM INSERTING IN TWO TABLES WITH RELATIONSHIP.MY TABLES ARE COMPANY AND EMAIL.
AND I AM TRYING TO SAVE COMPANY ID IN EMAIL TABLE.BUT IT RETURN ONLY LAST ID WHEN I AM INSERTING USING XML.HOW I CAN FIND @@IDENTITY FOR EACH ROW DURING XML INSERT.
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER PROCEDURE [dbo].[insertDataUsngXml]
@XML_TRANSACTIONDETAILS XML
as
DECLARE @i_Doc INT,
@DataID int
BEGIN transaction
EXEC SP_XML_PREPAREDOCUMENT @i_Doc OUTPUT,@XML_TRANSACTIONDETAILS
INSERT INTO tempdatainfo
(
companyname,website,country,contactperson,telphone,mobile,fax,region,status,username,date,category,urlorcatalog
)
SELECT
companyname,website,country,contactperson,telphone,mobile,fax,region,status,username,date,category,urlorcatalog
FROM OPENXML(@i_Doc,N’master/TRANSACTION’,2)
WITH
(
companyname varchar(100),
website varchar(100),
country varchar(100),
contactperson varchar(100),
telphone varchar(100),
mobile varchar(100),
fax varchar(100),
region varchar(100),
status varchar(100),
username varchar(100),
date datetime,
category varchar(100),
urlorcatalog varchar(100),
email varchar(100),
email1 varchar(100)
)
SELECT @DataID=@@IDENTITY
IF @@Error0 GOTO PROBLEM
insert into tempemailinfo
(
DataID,email,category,status,region,username,date
)
SELECT
@DataID, email,category,status,region,username,date
FROM OPENXML(@i_Doc,N’master/TRANSACTION’,2)
WITH
(
status varchar(100),
username varchar(100),
date datetime,
category varchar(100),
urlorcatalog varchar(100),
email varchar(100),
email1 varchar(100),
region varchar(100)
)
IF @@Error 0 GOTO PROBLEM
COMMIT TRANSACTION
RETURN 0
PROBLEM:
ROLLBACK TRANSACTION
RETURN 1
THANKS.
All features of SQL Server IDENTITY property of columns. And a handy procedure to monitor all Identity columns.
Nice Article
if my identifier column type is uniqueidentifier, identity or scopeidentity wont work. What can i do?
There is no system function available for uniqueidentifier. You need to assign newid() value to a variable and use that variable to insert to a table. This is the only way you can keep tract of last uniqueidentifier
hai yual,
You can use ‘OUTPUT Inserted.UID as Prim_Key’ inside the insert query.Then return the Prim_Key value.
ALTER PROCEDURE [dbo].[REGISTER_USER]
@username varchar(50),
@password varchar(50),
@bitfield bit,
@fname varchar(50),
@lname varchar(50)
AS
declare @role as varchar(50)
declare @fk int
BEGIN
if(@bitfield = ‘1’)
begin
SET @role=’Admin’;
insert into USER_INFO (UserName, Password, Role)values(@username, @password, @role);
select @fk = USER_ID from USER_INFO where UserName=@username AND Password=@password;
insert into ADMIN_DETAILS (User_id,Fname, Lname) values(@fk, @fname, @lname);
select SCOPE_IDENTITY();
select @@IDENTITY
end
if(@bitfield = ‘0’)
begin
SET @role=’Guest’;
insert into USER_INFO (UserName, Password, Role)values(@username, @password, @role);
select @fk = USER_ID from USER_INFO where UserName=@username AND Password=@password;
insert into GUEST_DETAILS (User_id,Fname, Lname) values(@fk, @fname, @lname);
select SCOPE_IDENTITY();
select @@IDENTITY
end
— Insert statements for procedure here
END
Here I want to return the scope identity of both USER_INFO and GUEST_DETAIL tables at a time.
How is it possible by using the scope_identity() function.
Hi Pinal,
can you explain about $IDENTITY, $ROWGUID,$action with example…
Thanks,
abhIShek Online4all
hi Pinal,
In the case of Uniqueidentifier ,returning empty from stored procedure while i am using @@IDENTITY or SCOPE_IDENTITY() for getting the primary key (which is unique identifier)of last inserted row.
Can you solve this problem?
Any other function available for retrieving the primary key value of last inserted row while using the uniqueidentifier type for primary key?
I just solved this problem by adding
‘OUTPUT Inserted.UID as Prim_Key’
inside the insert query…….Any other method?????
I’m having trouble figuring out how to gather multiple ids from an insert into from select. I need to create a new revision of an item and the item is multirelated to another table and that table is multi-related to other tables, I need to gather the new ids and relate them to the old ids so I can make sure all the relationships are connecting correctly. Doing it 1 at a time would work with a cursor but I’m trying avoid using a cursors. I would apriciate any help. Thank You
Have you read about the bug with @@IDENTITY and SCOPE_IDENTITY():
If the comments are right, this bug isn’t fixed until version 2012 comes out this year.
you are always able to explain concepts clearly. thank you so much for all your help.
Note that in help, it documents that rollback will remove the record, but not the identity change produced by the attempted insert. I’ve also verified all three identity methods return the same answer after a rollback. (Since my tests didn’t involve a trigger.)
Note also that IDENT_CURRENT will only work on the last record inserted on THIS server if the table is merge replicated. Hmmm, I’ve never tried ‘srvr.dbNm.dbo.tbl’ before. I could be wrong about that.
Hi pinal,
How to change identity column values (Update) ?
I have a MS SQL 2005 database with a table Test with column ID. ID is a identity column. I have rows in this table and all of them have their corresponding ID autoincremented value.
ID Name
1 abc
2 def
3 ghi
4 jkl
Now I would like to change every ID in this table like this:
ID Name
5001 abc
5002 def
3 ghi
4 jkl
But when I do this I get error:
Cannot update identity column ‘ID’.
I’ve tried with:
ALTER TABLE Test NOCHECK CONSTRAINT ALL
set identity_insert ID OFF/ON
But this does not solve this problem.
Hi Pinal,
I have 1 doubt.Pls clarify me. I created 1 table.In that i am inserting 5 records at a time using bulk insert in xml.I want the record status as 1 should display for those 5 records repeatedly and for next insertion it should display as 2 for next 5 records. Is it possible. pls help me with an example.
Do explicate update after bulk insert
Dear Pinal,
Can Scope_Identity(), ident_current() and @@identity gives always 100% correct result?
Not. It depends on the sessions they have used
Hi Pinal,
CREATE TABLE MyTable2
(
MyPK int identity,
MyColumn1 NVARCHAR(100),
MyColumn2 NVARCHAR(100)
)
INSERT INTO MyTable2
(MyColumn1,MyColumn2)
(select ‘A’ , ‘B’ from MyTable1)
Here I’m using select statement to insert records to MyTable2. MyTable1 contains say, 100 records.
now i need to use above to insert a GUID to MyTable2.MyColumn2 by combining MyPk (Current Value).
something like bellow
INSERT INTO MyTable2
(MyColumn1,MyColumn2)
(select ‘A’ , ‘B’+MyPk from MyTable1)
How can I do this? Inserting records should be from a select statement.
Remove the braces
INSERT INTO MyTable2
(MyColumn1,MyColumn2)
select ‘A’ , ‘B’+MyPk from MyTable1
MyPK is the identity value of the MyTable2. it does not contain in the MyTable1. here i need current inserting row identity. just removing braces gives Invalid column name ‘MyPK’
There is an important difference between IDENT_CURRENT versus SCOPE_IDENTITYand @@IDENTITY when referencing the IDENTITY value within an INSERT statement. See below for an example
CREATE TABLE [dbo].[t_TestIdentity](
[TestID] [bigint] IDENTITY(1,1) NOT NULL,
[TestIDCopy] [bigint] NULL,
[TestText] [nvarchar](32) NULL,
CONSTRAINT [PK_TestID] PRIMARY KEY CLUSTERED
([TestID] ASC)
)
GO
INSERT [dbo].[t_TestIdentity] ([TestIDCopy], [TestText]) VALUES (IDENT_CURRENT(‘[dbo].[t_TestIdentity]’), ‘Test1’)
INSERT [dbo].[t_TestIdentity] ([TestIDCopy], [TestText]) VALUES (SCOPE_IDENTITY(), ‘Test2’)
INSERT [dbo].[t_TestIdentity] ([TestIDCopy], [TestText]) VALUES (@@IDENTITY, ‘Test3’)
SELECT * FROM [dbo].[t_TestIdentity]
TestID TestIDCopy TestText
1 1 Test1
2 1 Test2
3 2 Test3
Tanx Paul. This is what i needed.