HOW TO: Refer to a Just-Inserted Record in a SQL Server 7.0 (319724)

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: To refer to a just-inserted record from an application, you can use different abilities from your data provider. The "How to Refer to a Just-Inserted Record in ADO" 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

1. Insert one or more records.
2. Run the following query to return the IDENTITY value of the last-inserted record:

   SELECT @@IDENTITY
   					

3. 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 = "Provider=sqloledb;Data Source=MyServer;Initial Catalog=Northwind;User Id=<username>;Password=<strong password>;"
      Cnxn.Open strCnxn

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

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

   ' View the added record:

   MsgBox "New record: " & rst!EmployeeID & " " & _
            rst!FirstName & " " & 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