sql server (alternate): Hi there
I have small problem with IDENT_CURRENT...
We have a table where PK is generated automatically.
What I want is to get that value and insert it to another,
corresponding table.
So here is what I am doing...
BEGIN TRAN
Insert Into table() --> PK is created.
SELECT IDENT_CURRENT('Table_Name') AS lastValue
COMMIT TRan

IT works fine, but there is a possibility to insert another record by
another user before I get my IDENT_CURRENT.
For instance:
BEGIN TRAN
Insert Into table() --> PK is created.
---->somebody else inserts another record into same table.
SELECT IDENT_CURRENT('Table_Name') AS lastValue
---->this is not the value that I generated....
COMMIT TRan

Take a look at @@IDENTITY and SCOPE_IDENTITY().

In this case, you will probably want to use the function
SCOPE_IDENTITY().

Thanks!!!!!

Use SCOPE_IDENTITY().

--
Tom

----------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
[quoted text, click to view]
Hi there
I have small problem with IDENT_CURRENT...
We have a table where PK is generated automatically.
What I want is to get that value and insert it to another,
corresponding table.
So here is what I am doing...
BEGIN TRAN
Insert Into table() --> PK is created.
SELECT IDENT_CURRENT('Table_Name') AS lastValue
COMMIT TRan

IT works fine, but there is a possibility to insert another record by
another user before I get my IDENT_CURRENT.
For instance:
BEGIN TRAN
Insert Into table() --> PK is created.
---->somebody else inserts another record into same table.
SELECT IDENT_CURRENT('Table_Name') AS lastValue
---->this is not the value that I generated....
COMMIT TRan

.... only if there is no trigger on the table that itself inserts into a
table with an identity.

--
Tom

----------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
[quoted text, click to view]

The proper way to do it is via @@IDENTITY - see BOL.

robert

[quoted text, click to view]

The proper way to do it is via @@IDENTITY - see BOL.

[quoted text, click to view]

Well, SCOPE_IDENTITY seems to be the solution. Since it's mentioned on
the same page in BOL I didn't bother to mention it.

Cheers