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

Tweet

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)
        rs.Close()
        Set rs = Nothing
RedirectURL = RedirectURL&"?client_id="&new_identity
Response.Redirect(RedirectURL)
%>

** 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.