Microsoft KB Archive/319724

= HOW TO: Refer to a Just-Inserted Record in a SQL Server 7.0 =

Article ID: 319724

Article Last Modified on 10/30/2003

-

APPLIES TO


 * Microsoft SQL Server 7.0 Standard Edition

-



This article was previously published under Q319724





IN THIS TASK
SUMMARY
 * How to Refer to a Just-Inserted Record by Using @@IDENTITY
 * How to Refer to a Just-Inserted Record in ADO



SUMMARY
This step-by-step article describes how to refer to a just-inserted record in a SQL Server 7.0. There is no specific function in SQL Server 7.0 to identify the last-inserted record; however, you can do this in Enterprise Manager by using Transact-SQL or in an application by using (for example) Microsoft ActiveX Data Objects (ADO) for data manipulation.

To refer to a just-inserted record in Transact-SQL, use the @@IDENTITY variable. For this to work, the table must have an IDENTITY column.

For additional information, click the article number below to view the article in the Microsoft Knowledge Base:

134660 INFO: Retrieving IDENTITY Value for Referential Integrity

To refer to a just-inserted record from an application, you can use different abilities from your data provider. The &quot;How to Refer to a Just-Inserted Record in ADO&quot; section in this article provides an example for an ADO data provider.

back to the top

How to Refer to a Just-Inserted Record by Using @@IDENTITY
 Insert one or more records.  Run the following query to return the IDENTITY value of the last-inserted record: SELECT @@IDENTITY   After the IDENTITY value is known, use the following query to access the value of any other column (or columns) in the record: SELECT [Your Specified Column Name] FROM [Your Specified Table Name] WHERE [Your Unique Identifier] = Value received on a step 2 

WARNING: @@IDENTITY returns the last-inserted identity value. You must understand the difference between the last-inserted record and the just-inserted record, and be careful when you use them. For example, if you have an IDENTITY column on Table [A] and one on Table [B], with an INSERT trigger on Table [A] that inserts into Table [B], the call to @@IDENTITY following the INSERT statement for Table [A] picks up the value for the INSERT into Table [B] (not Table [A]).

For example: insert TableA (ColA, ColB) values ('This', 'That') -- trigger fires and populates TableB select @@IDENTITY      -- obtains the value for TableB back to the top

How to Refer to a Just-Inserted Record in ADO
The following example demonstrates how to access the just-inserted record in ADO by using ADO Recordset at the application level:

NOTE: If you are using a non-ADO data provider in your application, you can use this scheme in same order. Dim Cnxn As ADODB.Connection Dim rst As ADODB.Recordset Dim strCnxn As String Dim strSQL As String Dim strFirstName As String Dim strLastName As String ' Open a connection Set Cnxn = New ADODB.Connection strCnxn = &quot;Provider=sqloledb;Data Source=MyServer;Initial Catalog=Northwind;User Id= ;Password= ;&quot; Cnxn.Open strCnxn

' Open Employees Table with a cursor that allows updates Set rst = New ADODB.Recordset strSQL = &quot;Employees&quot; rst.Open strSQL, strCnxn, adOpenKeyset, adLockOptimistic, adCmdTable 'modify it and add the name here strFirstName = &quot;firstname&quot; strLastName = &quot;lastname&quot;

' Add new record! rst.AddNew rst!FirstName = strFirstName rst!LastName = strLastName rst.Update

' View the added record:

MsgBox &quot;New record: &quot; & rst!EmployeeID & &quot; &quot; & _ rst!FirstName & &quot; &quot; & rst!LastName 'Print ' Here you can work with your just-inserted record. ' You can use the bookmark property to save the position of ' the current record and to return to that record at any  ' time or save EmployeeID for selecting you record by SQL  ' query 'Select * from Employees where EmployeeID =        ' saved_EmployeeID' ' Clean up     rst.Close Cnxn.Close Set rst = Nothing Set Cnxn = Nothing

back to the top

Keywords: kbhowto kbhowtomaster KB319724

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.