IDENT_CURRENT problem

[john]

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

[Tom Moreau]

Use SCOPE_IDENTITY().

--
Tom

----------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
"john" <j_****@hotmail.com> wrote in message
news:11**********************@j33g2000cwa.googlegr oups.com...
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

[Robert Klemme]

john wrote:

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

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

robert

[Tom Moreau]

.... 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
..
"Robert Klemme" <bo******@gmx.net> wrote in message
news:48************@individual.net...
john wrote:

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

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

robert

[figital]

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

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

[john]

Thanks!!!!!

[Robert Klemme]

Tom Moreau wrote:

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

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

robert