Getting Identity/Serial of Row Just Inserted?

This isn't so much purely a SQL Server question as a question on ASP.NET VB technique. In particular, I have a situation where I am either inserting a NEW row for a "profile table" (name, email, etc.) or Updating an existing one. In both cases, I need to create a new row in a related table which has the identity/serial column of the parent table as the primary key for the data to be inserted into this subsidiary table (for which there may be many rows inserted, all tying back to the parent).

At the time I do the update, of course, I have the identity/serial of the "parent" so it's easy to update/insert. However, if the profile is NEW, I need to capture the identity/serial which was inserted so as to use it for the child table insert. (I remember a call to an obscure function which was -- essentially -- "give me the identity/serial of that which was just INSERTed" but I am unable to locate equivalent functionality. (I have searched various online help files for "Insert serial", "Insert identity" and the like with no results.

Hints? Mahalos in advance ... :) KevInKauai

You can use the SCOPE_IDENTITY() function to retrieve the ID of the row just inserted. Check out books on line to read up some info on the function.

|||

@.@.IDENTITY

Returns the last-inserted identity value.

Syntax

@.@.IDENTITY

Return Types

numeric

Remarks

After an INSERT, SELECT INTO, or bulk copy statement completes, @.@.IDENTITY contains the last identity value generated by the statement. If the statement did not affect any tables with identity columns, @.@.IDENTITY returns NULL. If multiple rows are inserted, generating multiple identity values, @.@.IDENTITY returns the last identity value generated. If the statement fires one or more triggers that perform inserts that generate identity values, calling @.@.IDENTITY immediately after the statement returns the last identity value generated by the triggers. The @.@.IDENTITYvalue does not revert to a previous setting if the INSERT or SELECT INTO statementor bulk copy fails, or if the transaction is rolled back.

@.@.IDENTITY, SCOPE_IDENTITY, and IDENT_CURRENT are similar functions in that they return the last value inserted into the IDENTITY column of a table.

@.@.IDENTITY and SCOPE_IDENTITY will return the last identity value generated in any table in the current session. However, SCOPE_IDENTITY returns the value only within the current scope; @.@.IDENTITY is not limited to a specific scope.

IDENT_CURRENT is not limited by scope and session; it is limited to a specified table. IDENT_CURRENT returns the identity value generated for a specific table in any session and any scope. For more information, seeIDENT_CURRENT.

Examples

This example inserts a row into a table with an identity column and uses @.@.IDENTITY to display the identity value used in the new row.

INSERT INTO jobs (job_desc,min_lvl,max_lvl)
VALUES ('Accountant',12,125)
SELECT @.@.IDENTITY AS 'Identity'

|||

Perhaps I wasn't clear. I'm doing this in an ASP.NET application (..aspx) -- NOT in Transact-SQL -- where neither IDENTITY_CURRENT nor any of those other TRANSACT-SQL constants seems to be available.

KevInKauai

|||

KevInKauai:

NOT in Transact-SQL

Then perhaps you should convert it to a stored proc and youre life will be easier

|||

KevInKauai:

Perhaps I wasn't clear. I'm doing this in an ASP.NET application (..aspx) -- NOT in Transact-SQL -- where neither IDENTITY_CURRENT nor any of those other TRANSACT-SQL constants seems to be available.

KevInKauai

But it is still what you need to do. How exactly you implement it depends on how you're doing the code, but SCOPE_IDENTITY is what you need. (Mehedi: @.@.IDENTITY is generally not recommended, since concurrent operations can return the wrong value. SCOPE_IDENTITY() virtually always returns what you actually need.)

Are you running dynamic SQL in your ASP.NET application? Then you can tack on a call to SCOPE_IDENTITY() to the end of the query. Something like this:

string sql = "INSERT <row into primary table>; SELECT SCOPE_IDENTITY()"

Then execute the code and read the return value from the statement.

As David commented, this is all much easier and cleaner if you use a stored procedure, because then the new identity can either be returned as a single row, single column record set, or as the return value from the procedure.

In essence, you need to cause SQL Server to send you the value, which requires SCOPE_IDENTITY. But there are various ways to get it to do that.

Make sense?

Don

|||

Hi, Don - -

I prefer not to deal with Stored Procedures in general as they tend (to me) to be cumbersome and require extra steps rather than the "on-the-fly" development that I am presently dealing with.

That said, apprenting the "SCOPE_IDENTITY" to the INSERT seemed to not get an error, but where does the result come back?

1 SQL =String.Format("INSERT INTO [Parent] ([parentData]) " & _2 "VALUES ('{0}'); SELECT SCOPE_IDENTITY() ", _3 txtRowData.Text)45 SqlDataSource1.InsertCommand = SQL67Try8 SqlDataSource1.Insert()

The row got inserted (verified that), but now how do I get that identity? (Sorry to be such a blank here. This serial stuff was always obscure and I guess we can blame Chris Date for not including it more formally in the SQL definitiong.)

tia ... :) KevInKauai