Monday, 13 December 2010

SQL Server: basic functions (part 8) - @@IDENTITY


In this post I will introduce you a very tricky and useful function called @@IDENTITY. The function returns the last inserted identity value. As you may already know, the identity column in a Sql Server table is a numeric column that auto increments when a new value is inserted in the table itself. To put it simply, it is the ID column in your table. Now, what is the practical use of @@IDENTITY?

Imagine this scenario: you've built an insert page where the user can submit general information about a new customer to the customers table. The customers table is related to a products table through the customer id. In the product table, you relate every customer with a set of preferred products. The user, after inserting the new customer's general info, will be redirected to a new page in order to insert the customer's preferred products. What you need is to retrieve the newly created customer's ID and - tah dah! - you use @@IDENTITY.
After inserting the general info in the customers table, you automatically redirect the user to a new page, passing the newly created customer ID. Let's say you should open a new page named insert_prod.asp.
Dim RedirectURL
   RedirectURL = "insert_prod.asp"
Dim db, connection, rs, new_identity
    Set db = Server.CreateObject("adodb.connection")
    connection = "Driver={SQL Server};Server=server_path;Database=db_name;Uid=user;Pwd=password;"
      db.Open connection
        Set rs = db.Execute( "SELECT @@IDENTITY" )
          new_identity = rs(0)
        Set rs = Nothing
RedirectURL = RedirectURL&"?client_id="&new_identity

** Remember to change the above connection string with your connection string. **

What is the above snippet actually doing? It opens a connection to your database and retrieves the last inserted identity and then it uses the value to build the URL for the Response.Redirect command. Simple as that.
Now, in order to understand the @@IDENTITY more in depth, remember that you can use the function directly in your insert command like:
INSERT INTO customers (name, address, telephone)
VALUES ('XYZ', 'Easy Street', '012345')
SELECT @@IDENTITY as identity
And that's it.
Happy programming and please add your thoughts in the comment section below.

1 comment:

  1. When using a Dreamweaver Insert Behaviour sometimes you want to retrieve the last inserted identity value.

    Put this script between MM_editCmd.Execute and MM_editCmd.ActiveConnection.Close.

    MM_editCmd.CommandText = "SELECT @" & "@IDENTITY AS Ident"
    Set rsLastIdent = MM_editCmd.Execute
    If NOT rsLastIdent.EOF Then
    Session("LastIdent") = rsLastIdent("Ident")
    End If


Comments are moderated. I apologize if I don't publish comments immediately.

However, I do answer to all the comments.