Getting newly created key value

[L. Floyd]

I'm entering a new record into a table and need to find out what it's key
value is. How is this done (i.e., what is the SQL command)?

Thanks!

- larry

[D. Patrick Hoerter]

L.,

I suppose that depends...what is your key? Is it a slew of columns (if
so, you _already_ have it before you did your INSERT); If it's an IDENTITY
column, SELECT @@IDENTITY after the INSERT.

Regards,
D. Patrick Hoerter

L. Floyd wrote in message <399c0f55$1@news.devx.com>...
>
>I'm entering a new record into a table and need to find out what it's key
>value is. How is this done (i.e., what is the SQL command)?
>
>Thanks!
>
>- larry

[Simon Sellick]

"D. Patrick Hoerter" <dphwebAT@bellatlanticDOT.net> wrote:
>L.,
>
> I suppose that depends...what is your key? Is it a slew of columns (if
>so, you _already_ have it before you did your INSERT); If it's an IDENTITY
>column, SELECT @@IDENTITY after the INSERT.
>
>Regards,
>D. Patrick Hoerter
>
>L. Floyd wrote in message <399c0f55$1@news.devx.com>...
>>
>>I'm entering a new record into a table and need to find out what it's key
>>value is. How is this done (i.e., what is the SQL command)?
>>
>>Thanks!
>>
>>- larry
>
Is @@IDENTITY reliable? The T-SQL manual says it's a global variable - does
that mean that it could change if another INSERT is done between the INSERT
and the SELECT @@IDENTITY? If so, is there a way to make the two operations
happen without interruption? Or does global not really mean global in this
case?

Simon.

[PP]

@@Identity is very good if you dont use triggers in your database. If you
use triggers to enforce certain business rules and you use surrogate/artifical
keys on your tables then you might end up with the wrong @@IDENTITY value.


i.e. TableA has an trigger that inserts a row into TableB whenever a row
is inserted into TabblA, both tables PKey uses identity columns. Inserting
a row into TableA will then result in the @@IDENTITY being loaded with the
TableB value not with the TableA identity value.

You might think that you can then use max() to get the latest indentity value.
Not 100% reliable cause there is the "SET IDENTITY ON" statement that allows
you to add in any value you like, well depending on any underlying indexes.
So you will then have to expand the where clause to make certain that you
are retrieving the correct value.

This then leads onto the old religous war of "artifical vs natural keys"
which will never be resolved. The use of @@IDENTITY can be very useful but
be aware of the two pitfalls mention above.

Good Luck
PP











"Simon Sellick" <simon.sellick@tesco.net> wrote:
>
>"D. Patrick Hoerter" <dphwebAT@bellatlanticDOT.net> wrote:
>>L.,
>>
>> I suppose that depends...what is your key? Is it a slew of columns
(if
>>so, you _already_ have it before you did your INSERT); If it's an IDENTITY
>>column, SELECT @@IDENTITY after the INSERT.
>>
>>Regards,
>>D. Patrick Hoerter
>>
>>L. Floyd wrote in message <399c0f55$1@news.devx.com>...
>>>
>>>I'm entering a new record into a table and need to find out what it's
key
>>>value is. How is this done (i.e., what is the SQL command)?
>>>
>>>Thanks!
>>>
>>>- larry
>>
>Is @@IDENTITY reliable? The T-SQL manual says it's a global variable -
does
>that mean that it could change if another INSERT is done between the INSERT
>and the SELECT @@IDENTITY? If so, is there a way to make the two operations
>happen without interruption? Or does global not really mean global in this
>case?
>
>Simon.

[D. Patrick Hoerter]

PP,

The solution to all of those issues is to use an "ID Generator" table,
and hit it with serialized update transactions (incrementing by 1), then
selecting the new value.

Regards,
D. Patrick Hoerter

PP wrote in message <399d3aeb@news.devx.com>...
>
>@@Identity is very good if you dont use triggers in your database. If you
>use triggers to enforce certain business rules and you use
surrogate/artifical
>keys on your tables then you might end up with the wrong @@IDENTITY value.
>
>
>i.e. TableA has an trigger that inserts a row into TableB whenever a row
>is inserted into TabblA, both tables PKey uses identity columns. Inserting
>a row into TableA will then result in the @@IDENTITY being loaded with the
>TableB value not with the TableA identity value.
>
>You might think that you can then use max() to get the latest indentity
value.
>Not 100% reliable cause there is the "SET IDENTITY ON" statement that
allows
>you to add in any value you like, well depending on any underlying indexes.
>So you will then have to expand the where clause to make certain that you
>are retrieving the correct value.
>
>This then leads onto the old religous war of "artifical vs natural keys"
>which will never be resolved. The use of @@IDENTITY can be very useful but
>be aware of the two pitfalls mention above.
>
>Good Luck
> PP
>
>
>
>
>
>
>
>
>
>
>
>"Simon Sellick" <simon.sellick@tesco.net> wrote:
>>
>>"D. Patrick Hoerter" <dphwebAT@bellatlanticDOT.net> wrote:
>>>L.,
>>>
>>> I suppose that depends...what is your key? Is it a slew of columns
>(if
>>>so, you _already_ have it before you did your INSERT); If it's an
IDENTITY
>>>column, SELECT @@IDENTITY after the INSERT.
>>>
>>>Regards,
>>>D. Patrick Hoerter
>>>
>>>L. Floyd wrote in message <399c0f55$1@news.devx.com>...
>>>>
>>>>I'm entering a new record into a table and need to find out what it's
>key
>>>>value is. How is this done (i.e., what is the SQL command)?
>>>>
>>>>Thanks!
>>>>
>>>>- larry
>>>
>>Is @@IDENTITY reliable? The T-SQL manual says it's a global variable -
>does
>>that mean that it could change if another INSERT is done between the
INSERT
>>and the SELECT @@IDENTITY? If so, is there a way to make the two
operations
>>happen without interruption? Or does global not really mean global in
this
>>case?
>>
>>Simon.
>