Get the identity value inserted by the current SQL Server scope

Use this example to see the core idea, then work through the complete steps below. Code snippets are Classic ASP / VBScript or SQL Server T-SQL as labelled, not PHP.

Return the inserted row ID safely

SQL example

 CopySET NOCOUNT ON;
INSERT INTO dbo.auto (name, city) VALUES (N'Ana', N'Pune');
SELECT CAST(SCOPE_IDENTITY() AS int) AS new_id;

Use the same batch and connection. IDENT_CURRENT() is global to the table and unsafe for identifying your particular insertion when other users can insert concurrently.

When an INSERT creates an IDENTITY value, the application often needs that new ID immediately. The historical version of this page used IDENT_CURRENT() and described it as the ID “just inserted” by the current request. That is not safe under concurrent use.

Insert a row and return its identity

 Copy<%
Dim cmd, rs, newId
Set cmd = Server.CreateObject("ADODB.Command")
Set cmd.ActiveConnection = conn
cmd.CommandType = adCmdText
cmd.CommandText = "SET NOCOUNT ON; INSERT INTO dbo.auto (name, city) VALUES (?, ?); SELECT CAST(SCOPE_IDENTITY() AS int) AS new_id;"
cmd.Parameters.Append cmd.CreateParameter("pName", adVarWChar, adParamInput, 50, "Smo")
cmd.Parameters.Append cmd.CreateParameter("pCity", adVarWChar, adParamInput, 50, "Vizag")
Set rs = cmd.Execute
newId = rs("new_id")
Response.Write "New record ID: " & Server.HTMLEncode(CStr(newId))

rs.Close
Set rs = Nothing
Set cmd = Nothing
%>

This example assumes ADO constants such as adCmdText, adVarWChar and adParamInput are available, commonly through adovbs.inc. SET NOCOUNT ON suppresses the INSERT row-count message so the returned SELECT result is straightforward to consume through ADO.

If triggers insert rows into other identity tables, @@IDENTITY can also return a different value than the row created by your statement. SCOPE_IDENTITY() stays within the current session and scope.

Where IDENT_CURRENT() is useful

IDENT_CURRENT('dbo.auto') can be useful when you explicitly want the table's most recently generated identity regardless of which session created it. It should not be used to identify the row created by a particular visitor request.

Compare identity functions

Return a value from ADO

For the combined INSERT and SELECT, set NOCOUNT ON in the batch to avoid extra row-count results. Call SCOPE_IDENTITY() before leaving that same SQL scope. A database trigger or concurrent insertion is why IDENT_CURRENT() should not be used for this purpose.

Frequently asked questions

Are these queries for SQL Server or Access?

The SQL pages use SQL Server T-SQL. Access has different date functions, identity behavior, and SQL syntax.

Can I execute these statements directly on a live database?

Start in a test database. INSERT, UPDATE, DELETE, DROP and CREATE change data or schema.

How should Classic ASP send input to a query?

Use an ADODB.Command with typed, ordered parameters and encode database values when writing HTML.

Back to top ↑

Subhendu Mohapatra

Author & Instructor at plus2net

I write and maintain practical tutorials on Python, PHP, SQL, JavaScript, HTML, jQuery, and web development at plus2net. The tutorials focus on clear explanations, working examples, and code that readers can test and adapt while learning.

About the author   Watch on YouTube

Connect: