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 (http://blog.SQLAuthority.com)
very fine Article
Thanx
Regards
Rahul
CREATE TABLE Nbrs(n INT NOT NULL IDENTITY)
GO
SET NOCOUNT ON
INSERT Nbrs DEFAULT VALUES
WHILE SCOPE_IDENTITY() < 500
INSERT dbo.Nbrs DEFAULT VALUES
The above insert statement is not working in SQL server compact edition
Please help me to execute this query in sql server compact edition.
SCOPE_IDENTITY() function is not supported by SQL Compact edition.
Use @@IDENTITY
It s a fine article. it will com to help of all level programmer
thanks
So nice pinal, can u please give some DR & HA related topics in SQL DBA
Regards,
Subir
can we use scope_identity() for selecting the last inserted value in different server..
No.
Hai Pinal,..
How to get Identity value of an Updated row in SQl?
You can have it via update trigger
janani, You can use OUTPUT clause in UPDATE staement which is a new feature added to SQL server version 2005 onwards. check this link for examples.
http://blog.sqlauthority.com/2007/10/01/sql-server-2005-output-clause-example-and-explanation-with-insert-update-delete/
actually i hv to find out max value of one pramary key field.it will always a last inserted value.
so if my connection is opened for one server..then i am not able to find out the last inserted value with scope_identity() for that server.
so for this is there any other way .. or we have to use “select MAX(ID)”
If it is in a different server, you need to use
select max(id) as lat_id from table
Is there any way to retrieve the latest value of a field inserted in a table (apart from primary key field)…….if yes plz let me know…..
If you have identity column, datetime with default value of getdate() or a timestamp, it is easy
Can i use the IDENT_CURRENT to get the last record updated? I am trying to implement logging on a table with a trigger but I need to get the ID of the record that was updated.
In a trigger you can use UPDATE(col)
Thank you for the useful informations.
Can we use scope_identity() for selecting the last inserted value in different server.
No. It is not possible
hi,
Is it possible to find out the list of lattest used stored procedure in sql server, say for eg the list of 10 stored procedure being used in a page.
Is it possible to retrieve from the sysobjects ?
Yes using a DMV
SELECT TOP 10
last_execution_time ,
(SELECT SUBSTRING(text,statement_start_offset/2,(CASE WHEN statement_end_offset = -1 then LEN(CONVERT(nvarchar(max), text)) * 2 ELSE statement_end_offset end -statement_start_offset)/2) FROM sys.dm_exec_sql_text(sql_handle)) AS query_text
FROM sys.dm_exec_query_stats
ORDER BY last_execution_time DESC
Thanks, this is a great piece of information. I’m more familiar with MySQL, and having a function I’m used to using makes working with MS SQL much nicer!
I want to get the last inserted identity value of a table in a different server.
i want to use like :
select ident_current(‘server2.db1.dbo.table’)
if i give like this
select ident_current(‘db1.dbo.table’)
i am getting the result.
It is not possible to get it from a different server
You need to use
select max(col) from table
I need to retrieve identity column value from the below query:
INSERT INTO table-name(col1, col2) VALUES(val1, val2); SELECT SCOPE_IDENTITY()
using C + MS-SQL through ODBC
Please find the psuedo code that I have used:
hstmt = SQLAllocHandle
odbc_stmt = “INSERT INTO STUDENT (NAME, DIVISION) VALUES (?, ?); SELECT SCOPE_IDENTITY()”
SqlPrepare(hstmt, odbc_stmt)
SQLBindParameter(hstmt, 1, SQL_PARAM_INPUT, SQL_C_CHAR, &std_name, …
SQLBindParameter(hstmt, 2, SQL_PARAM_INPUT, SQL_C_CHAR, &std_division, …
SQLBindCol(hstmt, 3, SQL_C_SSHORT, &std_id, ….
SQLExecute(hstmt)
SQLFetch(hstmt)
————
Here ID is an identity column for the student table.
I am able to insert the row. But identity column value for the inserted row is not getting retrieved to std_id variable after SQLFetch.
Please help me to find out the problem?
thanks in advance….
@@identity will work in your case…
rajuneo,
Your code is not in T-SQL. I will be not able to help you out.
Regards,
Pinal Dave ( http://www.SQLAuthority.com )
Awesome just what I needed – thank you
Here is the problem, I create records using the identity number from the membership. I have checked the number is the same going in the db which is MS SQL 2005 however I can not retrieve records using the identity number, I have tried everything in grid view.
For example:
I have a drop down box list all users on selection it poplulates the grid view control.
However the grid view control does not populate I have even tried this manually pasting the identity number in but it returns no results. I am missing something here.
I write in VB VS2005 MS SQL 2005
Can you point me in the right direction to make this work? I am lost.
Can you post the code that you used in the VB application?
How do I get the id of the last inserted row if I don’t have an identity column? Of course, @@identity returns NULL in this case.
Thank you
If you have a datetime column with the default value of getdate() or a timestamp it is possible
Hi all,
I want to post the solution for my previous question (Index: 12). After digging for a lot of time, found missing piece from the code.
The key function is SQLMoreResults. It should be called just before SQLFetch.
The main purpose of SQLMoreResults is to find out if we have more than result set. We can always combine multiple queries in a single statement. In that scenario, we may expect multiple result sets. So to traverse from one result set to other, we require to call SQLMoreResults function.
thanks, rajuneo
INSERT INTO (SELECT Lists.ListCode ,Lists.ListName
FROM Lists)
VALUES (‘ListCode’,'ListName’)
plz tell me this statement run!!!!!!!!!
The correct code is
Can we use bigint for identity field?
Yes. You can use data type int, bigint, smallint, tinyint, or decimal or numeric with a scale of 0
What can I use to get the last Identity value like @@IDENTITY but over 2 connections.
I need to do multiple inserts and my program works with @@IDENTITY as the primary key the first time the program runs.
When I run it a 2nd time, I get the violation of primary key constraint and the statement has terminated error.
Please help!
Thanks,
Claudia
Also Ident_Current(‘tableName’) does not work in SQL Server 7
Thanks!
I run it but gives null value can u specify it.
It means data are not added to the table
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?
Thanks very much. It really helped me to understand difference between various options of identity.
My problem resolve. God Bless you.
I always have a first choice to search here.
Keep up the good work.
Joggee
Hello sir,
i have 1 doubt. how can i retrieve last 10 records from a particular table.
pls i want answer for this
If you have any unique column
select top 10 * from table
order by unique_col desc
hello sir,
please send me the query of fetch 10 random rows from table,
select top 10 * from table
order by newid()
Good Article
Excellent, it’s very clear and understandable article.
Thanks
Hi,
SELECT IDENT_CURRENT(’tablename’)
The above selects the last record from tablename.
How do i loop through the last 10 records?
Thanks
select top 10 * from tablename
order by identity_col desc
hi, i get last id of inserted data by using SCOPE_IDENTITY() from table1
Now my problem is how to insert the id from SCOPE_IDENTITY() to table 2
any guide are welcome
Thanks in advance
Nice and neat.
sha,
A safe way to do is to assign the SCOPE_IDENTITY value to a variable after the first INSERT statement. For example:
DECLARE @id INT
INSERT INTO firstTable(col1) SELECT 1
SELECT @id = SCOPE_IDENTITY()
INSERT INTO secondTable(col1, id) SELECT 1,@id
“Helvin
Hi,
SELECT IDENT_CURRENT(’tablename’)
The above selects the last record from tablename.
How do i loop through the last 10 records?
Thanks”
Helvin,
Probably the easiest option is to select the last 10 records. For example:
SELECT TOP 10 col1, col2, colN
FROM tablename
ORDER BY IdentityColumn
“Bharathidasan : how can i retrieve last 10 records from a particular table.”
Bharathidasan,
Try this:
SELECT TOP 10 col1, col2, colN
FROM tablename
ORDER BY IdentityColumn
“Ayyappan: Can we have more than one identity column in a table?”
Ayyappan,
There can be only one IDENTITY column per table.
“suresh: how can i add identity coloumn to existing coloum through query”
There is no direct way to do this. However, I recently found a script that helps to do this. http://iomegatrix.com/Phoenix/view_blog.aspx?_id_=34
hey, nice article. thnx!
Penal, with so many people following your blog you should consider putting some advertising on it…
thanks, it helped me to solve a tricky problem
thanks, nice one.
I am doing T-SQL perfomance analysis. In my case I have no issue in using eigther of this, Can anyone tell me that which one faster @@identity or SCOPE_IDENTITY() ??
You always have the answer I need – thank you!!!!
Hi pinale
i’m unable to access db it display’s timedout !
is any problem with deadlock or network as i checked by using trace files but i could not find any error msg on deadlocks.
give me your valuabl sugesstion.
Regards,
Harinath
@Harinath,
Were you able to log into SQL Server and you cannot access database ?
Please post exact error message.
What is state of database, is it online /suspect/restoring ?
Regards,
IM
[...] used in place of @@Idnetity has bug when run in Parallel Plan. You can read my explanation of @@IDENTITY vs SCOPE_IDENTITY() vs IDENT_CURRENT in earlier [...]
Hi,
I need to know, when i should use @@identity or scope_identity() inside any scope. What criteria i should keep in mind when i have to use any one of them in my program.
Thanks
Anil kumar dubey
Read the article again. It was already explained
Great post. Exactly what I was looking for.
i have used
SELECT IDENT_CURRENT(dbo.ITA_STORICO_ORDINI_DISTINTA)
but i have received this error
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier “dbo.ITA_STORICO_ORDINI_DISTINTA” could not be bound.
??????
You need to use a single quote
SELECT IDENT_CURRENT(‘dbo.ITA_STORICO_ORDINI_DISTINTA’)
SELECT CONVERT(INT, SCOPE_IDENTITY()) AS ID
will return the scope identity in for OdbcCmd object. Just doing a SELECT SCOPE_IDENTITY() results type cast errors if you are trying to get the ID out like this
ID = (int) cmd.ExecuteScalar();
@Mauro
Table name should be in single quotations like this,
SELECT IDENT_CURRENT(‘dbo.ITA_STORICO_ORDINI_DISTINTA’)
Try this, Should work.
IM.
hello ..
i am trying to make a sp how looks something like this,
what i want is to be able to get 2tables to create a new customer. .. i dosent work.. can you help me???
@Namn varchar(40),
@Adress varchar(50),
@Postnr varchar(6),
@Beskrivning varchar(max),
@Erbjudande varchar(max),
@kontaktUppgift varchar(30),
@CafeId int
AS
BEGIN
SET NOCOUNT ON;
BEGIN TRAN
declare @CafeId int
BEGIN TRY
INSERT INTO app.Cafe(Namn, Adress, PostNr, Beskrivning, Erbjudande)
VALUES(@Namn, @Adress,@Postnr,@Beskrivning,@Erbjudande)
Insert into app.Kontakt ( kontaktUppgift , CafeID)
values( @kontaktUppgift,@CafeId)
SET @CafeId = SCOPE_IDENTITY();
COMMIT TRAN
END TRY
BEGIN CATCH
–Återställer till innan begin tran
ROLLBACK TRAN
RAISERROR(‘fel i överföringen.’ ,16,1)
END CATCH
END
Tku….
very smart!
how to get identity value of last updated record?
Dave,
I read recently about a bug which could result in scope_identity() returning the wrong value. Do you know if this issue is still outstanding, and if so, should we avoid scope_identity(), or is it possible to mitigate the problem in some way?
The issue is described here:
https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=328811
@@IDENTITY is not working on SQL server 2005.
Why it is not working?
thnks.
@Dnyaneshwar
What is not working about @@IDENTITY? Please give an example of what you are trying, and what you expected.
None of the above is compatible with HSQLDB.
What if we want to junit the MSSQL DAOs using HSQLDB in memory DB?
How can we make the query compatible with both SQLServer and HSQLDB??
I have problem regarding this three
I have created sp for insert values in to three tables. Those three tables are like steps 1st table’s primary key keeps second table and second table primary key keeps the third table. Please follow following example to more clarification
insert Table 1 set ID1=SCOPE_IDENTITY()
Cursor for table 2
Insert table2 (foreign key ID1 ) SET ID2= SCOPE_IDENTITY()
Cursor for table 3
Insert table3 (foreign key ID3 ) SET ID2= SCOPE_IDENTITY()
END CURESOR 3
END CURESOR 2
Can I used Scope_Identity() for this scenario or any other solutions are welcome. Thanks for your advance reply.
Prateek:
Do a loop inside every insert. In that way, you’ll be able to retrieve SCOPE_IDENTITY() after each insert
loop:
begin
Insert 1
loop:
Insert2
loop:
begin
Insert3
end
begin
end
end
Hello All,
I wish to fetch last record from a table in sql server 2005 without using any group by or stored procedure is it possible
hello
would like to know the difference in terminology current session and current scope. Thanks in advance.
@Sachin Kalra
If proc A calls B and B calls C – Everything in all the 3 procedures are in the same session.
However, the SCOPE of a piece of code in PROC C will be limited only to that procedure (C). But it will be in the same session as A and B
Pinal,
This is very good, thank you very much
mathias
Thanks much, Pinal!!
You saved me again. Your posts always provide better explanations than MS
Following SQL Query is to get the LAST INSERTED ID if the column is auto increment using iBatis and SQL SERVER.
– your insert statement
select @@IDENTITY as value
I think it’s important to note here that while @@IDENTITY and SCOPE_IDENTITY() appear to work the same in the context of the same scope, that only @@IDENTITY will work if you happen to be trying to reference the current identity value within the same insert statement that is creating the IDENTITY value. SCOPE_IDENTITY() will return a NULL value in this case. I know you will ask why you would ever want to do this, but i just had a case where i had to do this and SCOPE_IDENTITY() did not work.
I’m convinced with the use of SCOPE_IDENTITY until one scenario i found out that SCOPE_IDENTITY doesn’t return the desired value if Instead of Triggers is implemented on the desired table.
But luckily, the @@IDENTITY works with that way.
However, still can’t understand the logic behind the scenario.
(Scenario Notes:)
INSTEAD OF TRIGGERS => checks the other tables in terms of required data, then, reinserts all fields with updated data.
@Ieuan0928
Could you post the scenario please. That is odd that id did not work.
Hello,
I have the same question like raksha posted
on July 9, 2007 at 2:07 am
Can you please help us?
Thanks and Regards,
Sandhya P.
Good explanations.
In one of my application SCOPE_IDENTITY() returns 1, some times. Below is the Code
INSERT INTO CUSTOMER(…)
SELECT … FROM #temp_table
SET @id = SCOPE_IDENTITY()
Note: Here CUSTOMER table has a AFTER trigger.
Any Guess..?
Thanks for producing a nice short article that gave the answer to what I was looking for.
how i know latest updated and inserted records in sql table.
Hi,
SET @id = SCOPE_IDENTITY() was the solution.
Thank you.
Regards,
Sandhya M. Potdar.
Again good article….
SCOPE_IDENTITY() is useful when multiple user inserting data in same table at same time??
so that will get separate identity for each row inserted.
??
or will get mess up
No, SCOPE_IDENTITY does not get messed up when multiple users insert data. You will get the identity value generated by your session only.
Thankx a lot
Thanks a lot. Thearticle was very useful. Keep writing such articles as it is very useful for Developer community…
Hi,
I am inserting multiple record. i am getting only last record id.i need latest inserted record id.
SCOPE_IDENTITY() it’s givining only 1 record.
example: I inserted 3 record,i need 3 records id.
Create PROCEDURE [dbo].[OrganisationSelectedType_insert]
(
@prmOrgTypeID nvarchar(50),
@prmOrganisationID nvarchar(50),
@prmOrganisationName nvarchar(50)
)
AS
BEGIN
INSERT INTO [OrganisationTypes] ([OrganisationID],[OrganisationName],[OrgTypeID])
SELECT @prmOrganisationID,@prmOrganisationName, data FROM [Split] (@prmOrgTypeID, ‘;’)
SELECT SCOPE_IDENTITY() As savedId
END
please help.
Thanks
Thanks for useful info…
I needed this for a long time for improving efficiency of my applications.
Thanks again…
Thansk for this article.it help me to solve my problem
thanks
I have a table of 25 records. Client mistakenly deleted the 15th row. Can u able to insert the same row with same identity? If possible how?
Could U Plz help me ….
You can do this by setting IDENTITY_INSERT to ON for the specific table. Once IDENTITY_INSERT is on, you can insert a value to the IDENTITY column.
Note that you need to set IDENTITY_INSERT to OFF when you are done.
I have presented an example here: http://beyondrelational.com/blogs/jacob/archive/2009/01/30/sql-server-how-do-i-insert-an-explicit-value-into-an-identity-column-how-do-i-update-the-value-of-an-identity-column.aspx
I have query like below
declare @str_CustomerID int
Insert into IMDECONP38.[Customer].dbo.CustomerMaster
( CustomerName , CustomerAddress , CustomerEmail , CustomerPhone )
values ( ‘werw12e’ , ‘jkj12kj’ , ’3212423sdf’ , ’1212121′
)
select @str_CustomerID= scope_identity()
after execution it returns null in my parameter
Is there an identity column on the table?
May we use scope_identity() for selecting the last inserted value in different server.
hi i am kapil. I am new on this site. this is a very good article.
hi , i m amol. It is nice site to solve my problems. Thank’s
can’t we get the last updated id of a table?? thanx
As we get in the insertion , can’t we get the id (primary key) of a table when it’s updated??
I only need to get the last updated primary key. Thanx
It is possible Only if you have a timestamp column
only need to get the last updated primary key.
Thanks
Please answer ASAP.
Hi Pinal,
i am adding multiple rows in a table using XML. How do i get @@identity of each row inserted?
Please help……
Also please tell me is is feasible or not.
In SQL server 2005 and 2008 you can use a new feature: OUTPUT clause. You will get right values without mistakes.
Here is the link.
http://technet.microsoft.com/en-us/library/ms177564.aspx
This feature really helps to solve all Identity column issues along with other benefits.
Hi…Habydij Raja
We cant use scope_identity() for selecting the last inserted value in different server.
Hi! can anyone help me..
Im creating a CAI (computer aided instruction) on VB6
and i just want to view a last record of ID and increment it by 1? can anyone please help me..?
Thanks a lot… :D
Use scope_identity() and assign it to the variable
hi,
can you answer the query for this:
The database scheme consists of four tables:
Product(maker, model, type)
PC(code, model, speed, ram, hd, cd, price)
Laptop(code, model, speed, ram, hd, screen, price)
Printer(code, model, color, type, price)
The table “Product” includes information about the maker, model number, and type (‘PC’, ‘Laptop’, or ‘Printer’). It is assumed that model numbers in the Product table are unique for all the makers and product types. Each PC uniquely specifying by a code in the table “PC” is characterized by model (foreign key referencing to Product table), speed (of the processor in MHz), total amount of RAM – ram (in Mb), hard disk drive capacity – hd (in Gb), CD ROM speed – cd (for example, ’4x’), and the price. The table “Laptop” is similar to that one of PCs except for the CD ROM speed, which is replaced by the screen size – screen (in inches). For each printer in the table “Printer” it is told whether the printer is color or not (color attribute is ‘y’ for color printers; otherwise it is ‘n’), printer type (laser, jet, or matrix), and the price.
* Find the pairs of PC models having similar speeds and RAM. As a result, each resulting pair is shown only once, i.e. (i, j) but not (j, i).
Result set: model with higher number, model with lower number, speed, and RAM
thank u
very helpful
hi
i have a doubt pls clarify it….
i created a table with an identity column say a student table , student id as IDENTITY(100,1)… so at each insert the student id will be incremented by 1 starting from 100 and goes on 101,102……….
actually what i need is the student id should be like this “S100,S101,S102″…. is this possible using IDENTITY….
how can i get this …..
thanks in advance…
Deepan
Very Nice Pinal
Shyam
This is the third time I met your blog while searching sth on google so I wanted to thank you this time. Very informative article. It helps me to solve my problem.
Hi Pinal,
Your article is just awesome.
I have a small query regarding the performance of the Three Clauses used above:
Can you please tell which one is better from performance point of view
@@IDENTITY / SCOPE_IDENTITY() / IDENT_CURRENT(‘tablename’)
Waiting for your reply.
Regards
Gaurav Girdher
how to get the last inserted record in sql server
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. http://www.sqllion.com/2011/08/identity-in-sql/
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
[...] Server Interview Questions and Answers ISBN: 1466405643 Page#35 @@IDENTITY vs SCOPE_IDENTITY() vs IDENT_CURRENT – Retrieve Last Inserted Identity of Record Reseed Identity of Table – Table Missing Identity Values – Gap in Identity Column Discussion [...]
Have you read about the bug with @@IDENTITY and SCOPE_IDENTITY():
https://connect.microsoft.com/SQLServer/feedback/details/328811/scope-identity-sometimes-returns-incorrect-value
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.