Getting value for identity Col for insert - sql 2005

Question

Post reply

Getting value for identity Col for insert - sql 2005

Rookee DBA

SSCrazy

Points: 2188
More actions
October 28, 2008 at 5:26 am

#96569

We are inserting values in table A and there is a ID col on the smae table. After inserting the value, we need to get the value of the ID col for which insert fired successfully. We have used scope_identity but the same is casuing issues. Could you please let me know what is the safe way of get the ID col value after insert. ID col is auto increment.
cheers
Siddarth

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Grant Fritchey SSC Guru Points: 398862 More actions · Answer 1

Best way to do that is to use the OUTPUT clause.

From BOL:

USE AdventureWorks;

GO

DECLARE @MyTableVar table( ScrapReasonID smallint,

Name varchar(50),

ModifiedDate datetime);

INSERT Production.ScrapReason

OUTPUT INSERTED.ScrapReasonID, INSERTED.Name, INSERTED.ModifiedDate

INTO @MyTableVar

VALUES (N'Operator error', GETDATE());

--Display the result set of the table variable.

SELECT ScrapReasonID, Name, ModifiedDate FROM @MyTableVar;

--Display the result set of the table.

SELECT ScrapReasonID, Name, ModifiedDate

FROM Production.ScrapReason;

GO

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

SQLZ SSChampion Points: 12872 More actions · Answer 2

siddartha pal (10/28/2008)
We are inserting values in table A and there is a ID col on the smae table. After inserting the value, we need to get the value of the ID col for which insert fired successfully. We have used scope_identity but the same is casuing issues. Could you please let me know what is the safe way of get the ID col value after insert. ID col is auto increment.
cheers
Siddarth

If all you want is the ID column, which is an identity column then I'd use SCOPE_IDENTITY() rather than using the OUTPUT, which (to me at least) appears a bit long-winded in this scenario.

So the question is, what issues is SCOPE_IDENTITY causing for you?

Karl
source control for SQL Server

Rookee DBA SSCrazy Points: 2188 More actions · Answer 3

It appears from the records in db that we are not getting the correct value. Is that possible that some other transaction is also inserting the value after our insert and because of the same when we call SCOPE_Identity we are getting wrong values?

SIDDARTH

SQLZ SSChampion Points: 12872 More actions · Answer 4

siddartha pal (10/28/2008)
It appears from the records in db that we are not getting the correct value. Is that possible that some other transaction is also inserting the value after our insert and because of the same when we call SCOPE_Identity we are getting wrong values?
SIDDARTH

I'm not sure how this is going wrong.

SCOPE_IDENTITY is limited to the current session and scope so no other session should interfere with this. Remember that SCOPE_IDENTITY (when called from a procedure) will ignore inserts that occur inside triggers (i.e. out of the current scope).

Karl
source control for SQL Server

Jerry Hung SSChampion Points: 12968 More actions · Answer 5

Hi SIDDARTH

Perhaps an example would help as well?

Did you try the OUTPUT clause?

I always thought SCOPE_IDENTITY only works for the last single record inserted

so if current ID is 100, increment of 1, you insert 50 rows, SCOPE_IDENTITY will give you id 150 whereas OUTPUT can give you all 50 new ID's created

-- Jerry Hung on SQL 2008

SET XACT_ABORT, NOCOUNT ON

BEGIN TRAN

USE AdventureWorks2008

GO

DECLARE @MyTableVar table( ScrapReasonID smallint,

Name varchar(50),

ModifiedDate datetime);

INSERT Production.ScrapReason

OUTPUT INSERTED.ScrapReasonID, INSERTED.Name, INSERTED.ModifiedDate

INTO @MyTableVar

VALUES

(N'Operator error', GETDATE()),

(N'Jerry 1', GETDATE()),

(N'Jerry 2', GETDATE())

--Display the result set of the table variable.

SELECT ScrapReasonID, Name, ModifiedDate FROM @MyTableVar;-- shows 23, 24, 25

--Display the result set of the table.

SELECT ScrapReasonID, Name, ModifiedDate

FROM Production.ScrapReason;

select SCOPE_IDENTITY() -- shows 25

GO

ROLLBACK

--COMMIT

SQLServerNewbieMCITP: Database Administrator SQL Server 2005

Rookee DBA SSCrazy Points: 2188 More actions · Answer 6

We also have the same understanding so we are not getting why we are not getting the right value. I think we need to analyse again and need to ensure that what we have concluded in correct.

And thanks alot for your valuable time.

Cheers

Siddarth

Rookee DBA SSCrazy Points: 2188 More actions · Answer 7

Rookee DBA

SSCrazy

Points: 2188

October 28, 2008 at 7:53 am

#890629

Hi Jerry

thanks a lot for the same.

Cheers

Siddarth