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
To 18: Yogesh:-
Your statement is wrong. If you want to insert data from one table into another, check this out:
insert into table2(select table2.t2id, t2name) select * from table1
It should be
insert into table2(id,name)
select id,name from table1
hi
how to find Current row(Primary key)?
Hi,
I have a query it will be a great help for me.
I have 3 tables
table1
( T1id identity (1,1) PK, Name)
table 2
(t2id PK, T1id, Address)
table3
(t3id PK,T1id,Phone)
Now I need to insert data like this
begin transaction
—multiple rows are inserted from xml insert in table 1
for i to 100
insert into table1 values(‘adsf’)
next
for j to 100
—Similarly multiple rows are inserted from xml insert in table 2 and table 3
— but I cannot get the t1ID which is in table 1 because it has multiple identity now after xml insert
— so here in table2 and table 3 the t1ID cannot be retrieved from scope_Identity()
— so we need some what like array of scope_Identity()
next
commit transaction
on error
rollback transaction
— i think you got my point
— can you please reply me at my mail address provided
— Thank you in advance
Can you please solve this problem in sql server 2005
This is a interesting question “Prateek”.
How do we track a list of SCOPE_IDENTITY() values produced by a single insert statement.
I had the same issue, did you got any solution for this ?
Hello Pavan,
Log the SCOPE_IDENTITY() value in some table if you want to use that later time. You can use trigger for this.
We can provide a better resolution if you can explain you requirement.
Regards,
Pinal Dave
Hi Pinal,
Thanks for the response.
Some how I am not able to reply your resposne. A message ‘discarded’ is display when I submit the comments. Is there any other way that i could post you the details
Regards
pavan
Hello Prateek,
Instead of using two saperate FOR loops, use one to insert in table1, table2 and table3. This way you woud have SCOPE_IDENTITY() values for each insert statement to be used in table2 and table3.
Regards,
Pinal Dave
good article
Great article.
One question though:
Can we use scope_identity() for selecting the last inserted value in different server?
No. You cant use it for a different server
Dear Pinal,
Firstly I would like to thank you for this wonderful site.
Secondly I have a question to post.
What is the syntax to alter a table to add identity property (with seed and incremental values) to an already existing column in that table?
You cant alter the existing column to have identity property
The only way is to create a new column with identity,drop old column and rename new column to old column
Thanks in advance for any reply.
I have been running replicated servers using merge replication and SQL 2000 for about a year without issue. I have several sp’s that used @@Identity
I upgraded to SQL 2005 last week.
Guess what – those SP’s are now intermittently returning the identity of a table from an associated repl trigger.
Have you seen or heard of this occurring after an upgrade?
Thank goodness I stumbled onto your article and was able to fix all instances of @@Identity in my SP’s. (Now I have to replicate the sp’s manually off-hours….)
thanks
Wes
I want to select the records from a table which are updated or added in the last one month (table doesn’t have any datetime field and I can’t change the structure). Is it possible to write a sql query for that?
Without a datetime column, it is not possible
to retreive the last entered record in table
very informative article……..thanks a lot…..
but it will be more easy to understand if examples had been put here………
Hi Viswanath,
I dont think that you can add the Identity to the existing coloumn. You need to drop the table and recreate with Identity. You can add a new column with Identity in alter table.
– Vijay Anand Kannan
Hi, Pinal,
The Solution you have given seems very problem solving. I will try this for sure. My problem is this that: I have an web application, problem is that; when more than one user enterd data on the same time the data entered wrongly i.e. user 1 data save in user2 and user 2 data saves in itself(user2)
You need to have a Transaction for better control
Iam having a real strange issue with both the @@identity and the scope_identity(). The problem is running the same insert on two different machines with the exact DBs and the result on machine is as expected and on the other i get null values for both. unlike the examples quoted by the users here, iam using a select statement instead of values list to insert.
And Iam using SQL 2005 w/ SP2 on both the machines. (The backward compatibility levels for both are SQL server 2000 (80) if you need to know)
Here is what Iam doing:
INSERT INTO TEMPTABLE ( column1, column2)
SELECT t1, t2 FROMNewTable where t3 = ‘somevalue’;
SELECT @@IDENTITY;
INSERT INTO TEMPTABLE ( column1, column2)
SELECT t1, t2 FROMNewTable where t3 = ‘somevalue’;
SELECT Scope_Identity();
INSERT INTO TEMPTABLE ( column1, column2)
SELECT t1, t2 FROMNewTable where t3 = ‘somevalue’;
SELECT Ident_Currenty(‘TEMPTABLE’);
One machine gives me same value on all three statements, while the other gives me null on first two and gives me a value on the third.
Any insights will be appreciated.
– Mahee
It means data are not added to the table as there is no data returned for this
SELECT t1, t2 FROMNewTable where t3 = ‘somevalue’;
At last! Someone who unedsratdns! Thanks for posting!
hi
Please help me out.
In my stored proc, I use @ident_current of a table(Table X) as a default value for my table variables (Temp Table)
Now Since Im creating a Table Variable, I want one of its columns to have a default value of the identity of the Table X , Now when creating this Table Variable, it will not accept
a variable as a default hence forcing me to use Ident_Current(Table X) as the default. I know that this can produce erroneous data as it applies to any session .. is there another way out? I cant use @@Identity as there are 2 more Table Variables created after the insertion of Table X – Hence will pick up wrong identity value ….
here’s my code
——————————————–
INSERT INTO TABLE X
DECLARE A TABLE A
INSERT INTO TABLE A
DECLARE TABLE B
INSERT INTO TABLE B
DECLARE C TABLE (COL1, COL2 INT DEFAULT IDENT_CURRENT( ‘TABLE X’ ),COL3)
————————————————————
The code you posted is correct
Did you get any problem?
The main problem is, I can’t get the query result from 2 different connection with different table.
But with the same connection/server, I can get the query result.
SELECT SERVER1enroll.ID
FROM SERVER1enroll
WHERE SERVER1enroll.ID NOT IN (SELECT SERVER2enroll.ID FROM SERVER2enroll)
And is there a shortcut for that SQLcode to make a faster query than that.
Can anyone solve that?
I’m using 2 different connection, Server1 on my PC, Server2 is on the other PC.
I’m using VB9
Can anyone share some code for development testing?
how can i add identity coloumn to existing coloum through query
It is not possible. You need to create a new column with identity property
i have user id table i want retrive last user id from that table
i need sql query for pls help me
If the userid is in the increasing order,
select max(userid) as userid from table
nice one .. thnk you
Can we have more than one identity column in a table?
if yes, please tell me how we can do that?
Thanks
It is not possible. Why do you need this?