How to get an Identity value with SQL Server 2005

re: How to get an Identity value with SQL Server 2005

sourabh — Tue, 25 Aug 2009 04:48:02 GMT

select max(identity column) from tablename

example:

create table employees

(id int identity(1,1))

select max(id) from employees

re: How to get an Identity value with SQL Server 2005

Dreamer — Tue, 10 Feb 2009 08:35:19 GMT

Other way simply take result from system table

SELECT last_value FROM sys.identity_columns

WHERE [object_id] = object_id('table_name')

I read in some books that, system talbes can be diferent in different versions of SQL servers, so be aware. Anyway I use it and didn't get any problems yet. Maybe it's better to find some stored procedure like sp_*, but I didn't.

msdn.microsoft.com/.../ms187334.aspx

re: How to get an Identity value with SQL Server 2005

Dreamer — Sat, 01 Nov 2008 11:58:37 GMT

To get last value of identity in MsSQL:

use DataBaseName

Select Count(*) from TableName

select IDENT_CURRENT('TableName') as LastIdentity

comment:

we need IDENT_CURRENT function, but it gives value of last tabe with which we were dealing lately. So then we choise database, doing anything with table which we need, and then using IDENT_CURRENT.

msdn.microsoft.com/.../aa933217(SQL.80).aspx

key words: How to get last increment/identity of column or table?

re: How to get an Identity value with SQL Server 2005

Shantha — Fri, 10 Oct 2008 06:14:49 GMT

First Sample is good one!

re: How to get an Identity value with SQL Server 2005

sabuv — Wed, 08 Oct 2008 16:16:50 GMT

i want to get the current identity value of a table before insert the next row.

re: How to get an Identity value with SQL Server 2005

Kila — Thu, 29 May 2008 18:23:25 GMT

I just have to add that SCOPE_IDENTITY does NOT work with the uniqueidentifier data type, so OUTPUT is useful in that situation. SCOPE_IDENTITY only works for numeric columns

re: How to get an Identity value with SQL Server 2005

rohit — Fri, 04 Jan 2008 14:10:12 GMT

@@Identity returns the last identity value inserted in *any table

re: How to get an Identity value with SQL Server 2005

Sam — Mon, 01 Oct 2007 17:29:52 GMT

How can i get the Identity for the last updated record?

re: How to get an Identity value with SQL Server 2005

Kjetil — Mon, 22 Jan 2007 14:54:02 GMT

Any idea on how to use this in conjunction with ADO.NET? Using the old "scope_identity()" and return value from stored procedure, one could simply map this to a SqlDataSource object using e.g. an InsertParameter with Direction="Output". But I don't see any way to accomplish this using the new output clause in SQL Server 2005....

re: How to get an Identity value with SQL Server 2005

Mike Wood — Tue, 25 Apr 2006 11:13:00 GMT

Okay, whoops. I need to read more, the author does point out Scope_Identity is better to use. Sorry about that.