By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,412 Members | 1,630 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,412 IT Pros & Developers. It's quick & easy.

IDENT_CURRENT problem

P: n/a
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

Mar 21 '06 #1
Share this Question
Share on Google+
6 Replies


P: n/a
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

Mar 21 '06 #2

P: n/a
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
Mar 21 '06 #3

P: n/a
.... 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

Mar 21 '06 #4

P: n/a
Take a look at @@IDENTITY and SCOPE_IDENTITY().

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

Mar 21 '06 #5

P: n/a
Thanks!!!!!

Mar 21 '06 #6

P: n/a
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
Mar 22 '06 #7

This discussion thread is closed

Replies have been disabled for this discussion.