You have to be careful on this one. Books OnLine shows 3 different methods for retrieving this value. Each could cause problems.

1) select @@IDENTITY

This method will return the last identity value generated in any table in the current session not limited to scope. Scope, in this case, is inserts in the same command, stored procedure or trigger. The problem with this is that if there are insert triggers on the table you inserted a record into, and at least one of those tables has an identity field, you will get the identiy value from the LAST table where a record was inserted, not necessarily your main table.

2) select IDENT_CURRENT([tablename])

This will return the last indentity value inserted into the table. If another person inserts a record between your insert and calling this function, you'll get the identiy value of their record, not yours.

3) select SCOPE_IDENTITY()

This is probably the best choice. It retrieves the last identity value generated in any table in the current session, similar to the @@IDENTITY. However, this function is limited to inserts in the same scope. Since triggers create a new scope, inserts into other tables are not "seen" by SCOPE_IDENTIY(). However, if you are calling a stored procedure that has multiple insert commands, inserting records into different tables with identity fields, these inserts are in the same scope. In this case, SCOPE_IDENTITY() will return the last identity value inserted.

A method that I have used in the past is to construct a select statement using a combination of fields just inserted that uniquiely identifies the record in the WHERE clause. Send that select statment retrieving the identity field.




>I'm using VFP 6 and SQL 2000. Almost all SQL tables have an identity column for the pk. When I add a new record to a view, is there a direct way to retrieve the PK value that was assigned by SQL? I currently do some pretty serious gyrations to pull it off. Must be a quicker way...
>
>TIA