Capture the ID of the last record entered and use in an Update

I'm entering a Selection record for a partiuclar lotID,

Once entered, I need to obtain its SelectionID then use it to update a another field within that record.

Here's what I've been doing...

--insert values into a testchangeorders table

INSERT

INTO testchangeordersVALUES(2,3,3,3,1,'red',0,5)

--Find the SelectionsID of the last record created for that partiuclar LotID

SELECT

MAX(SelectionsID)

FROM

testchangeorders

WHERE

LotID= 2

--Once located, I was trying to update a field called uniqueID with a contantination of '3-' & the record's SelectionsID

UPDATE

testchangeorders

SET

UniqueID=('3-'& SelectionID

WHERE

SelectionsID=SELECTMAX(SelectionsID)AND LotID= 2)

If your SelectionsID is a identity field Use

select@.@.identity

after your insert statement.It returns the last identity value created in the cureent scope.

Have this code in a stored procedure along with the insert statement

Hope this will help you. Let me Know if you need any further help

|||Actually, you'll want to use SELECT SCOPE_IDENTITY() if you want it limited to the current scope. @.@.IDENTITY is only limited to the current session, not the current scope.|||

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

For example, you have two tables, T1 and T2, and an INSERT trigger defined on T1. When a row is inserted to T1, the trigger fires and inserts a row in T2. This scenario illustrates two scopes: the insert on T1, and the insert on T2 as a result of the trigger.

Assuming that both T1 and T2 have IDENTITY columns, @.@.IDENTITY and SCOPE_IDENTITY will return different values at the end of an INSERT statement on T1.

@.@.IDENTITY will return the last IDENTITY column value inserted across any scope in the current session, which is the value inserted in T2.

SCOPE_IDENTITY() will return the IDENTITY value inserted in T1, which was the last INSERT that occurred in the same scope. The SCOPE_IDENTITY() function will return the NULL value if the function is invoked before any insert statements into an identity column occur in the scope.

See Examples for an illustration.

Examples

This example creates two tables,TZ andTY, and an INSERT trigger onTZ. When a row is inserted to tableTZ, the trigger (Ztrig) fires and inserts a row inTY.

USE tempdb
GO
CREATE TABLE TZ (
 Z_id int IDENTITY(1,1)PRIMARY KEY,
 Z_name varchar(20) NOT NULL)
INSERT TZ
 VALUES ('Lisa')
INSERT TZ
 VALUES ('Mike')
INSERT TZ
 VALUES ('Carla')
SELECT * FROM TZ
--Result set: This is how table TZ looks
Z_id Z_name
----
1 Lisa
2 Mike
3 Carla
CREATE TABLE TY (
 Y_id int IDENTITY(100,5)PRIMARY KEY,
 Y_name varchar(20) NULL)
INSERT TY (Y_name)
 VALUES ('boathouse')
INSERT TY (Y_name)
 VALUES ('rocks')
INSERT TY (Y_name)
 VALUES ('elevator')
SELECT * FROM TY
--Result set: This is how TY looks:
Y_id Y_name
-----
100 boathouse
105 rocks
110 elevator
/*Create the trigger that inserts a row in table TY
when a row is inserted in table TZ*/
CREATE TRIGGER Ztrig
ON TZ
FOR INSERT AS
 BEGIN
 INSERT TY VALUES ('')
 END
/*FIRE the trigger and find out what identity values you get
with the @.@.IDENTITY and SCOPE_IDENTITY functions*/
INSERT TZ VALUES ('Rosalie')
SELECT SCOPE_IDENTITY() AS [SCOPE_IDENTITY]
GO
SELECT @.@.IDENTITY AS [@.@.IDENTITY]
GO
--Here is the result set.
SCOPE_IDENTITY
4
/*SCOPE_IDENTITY returned the last identity value in the same scope, which was the insert on table TZ*/
@.@.IDENTITY
115
/*@.@.IDENTITY returned the last identity value inserted to TY by the trigger, which fired due to an earlier insert on TZ*/

SQL Server Books Online

HTH 

|||

Thanks-each of you who responded.

I read and reread the last post explaining @.@.IDENTITY and SELECT SCOPE_IDENTITY(). I'm a little fuzzy as to why one would even use @.@.IDENTITY, unless in my case it doesn't matter.

I'm using the following code and if I use @.@.IDENTITY is seems to work just as well what I have below

INSERT

INTO testchangeordersVALUES(2,3,3,3,1,'red',0,5)

UPDATE

testchangeordersSET UniqueID='3-'+CONVERT(varchar,SCOPE_IDENTITY())

WHERE

SelectionsID=SCOPE_IDENTITY()

It updates the UniqueID with a "3-#" (PK) just as I want it to ie., 3-71

I guess my question is, in my case many users will be hitting this at the same time:

If user A fires it and creates ID# 22, the UniqueID should be 3-22

If user B fires it and creates ID#23, the UniqueID should be 3-23

By going with Scope_Identity() does that guarantee that records are being updated correctly? I understand why to use scope if you are updating two tables but don't know if it matters if you are working in the same table (which may be why I was advised to use @.@.IDENTITY in the first place).

I think I'm ok because the condition is WHERE SelectionsID = SCOPE_IDENTITY and that should make sure the correct one is updated.

|||

I'd say 99.9% of the time when someone wants to get the last identity created, they want to be using SCOPE_IDENTITY(). You don't need to worry about either of them getting confused by concurrent users, though, since they are both per-session. The difference generally comes into play when you have triggers on tables that will automatically create records in other tables after an action in your sproc. In that case @.@.IDENTITY will get the last identity created by the session *in any table*.

|||

Have a look into this.

http://dlanouette.blogspot.com/2005/05/sql-identity-vs-scopeidentity.html

http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=10434

Hope this may help you

|||

I'm clear now thanks.

AshokRaja - thanks too. That first link in particular was good.