Article: Alternatives to @@IDENTITY in SQL Server 2000

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Site Related Forums

Article Discussion

Article: Alternatives to @@IDENTITY in SQL Server 2000

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2000-08-08 : 17:46:37

Mark writes: "I was asked a question today by a developer having problems getting the right identity value from @@identity when a table has a trigger which has an additional insert - Post the insert statement the select @@identity returns the wrong value (which is behaviour I would expect).

Is there a trick to get round this, apart from not using triggers and/or not using identity columns - which is what I suggested ..."

Prior to SQL Server 2000, the answer was "no". However, SQL Server 2000 adds two cool new functions to help you get around this problem. Read on...

Article Link.

Henrysu
Starting Member

1 Post

Posted - 2002-06-24 : 16:31:39

Hi,

It's nice to see your article, I have another question, can I get the last result regarding to the specified table and only on my connection? cause I have a table, I wanna know the last identity I have inserted, it sounds no way to do that.

Henry

graz
Chief SQLTeam Crack Dealer

4149 Posts

Posted - 2002-06-24 : 17:13:26

This article should answer your question (http://www.sqlteam.com/item.asp?ItemID=8003).

===============================================
Creating tomorrow's legacy systems today.
One crisis at a time.

dim1r
Starting Member

1 Post

Posted - 2002-07-25 : 07:07:40

I use batch updates/inserts in my program so I need to generate unique values prior insertions.

How can create a procedure which will return new incremental numbers after every calls for all sessions and transaction ?

I know that there are special objects in oracle and interbase servers.

versaggi
Starting Member

1 Post

Posted - 2003-05-28 : 22:32:09

Wonderful article! A big help....kudos!

rawheiser
Starting Member

1 Post

Posted - 2004-09-07 : 13:45:23

How to reset @@IDENTITY within a trigger in SQL 7.0 .....

CREATE TRIGGER dbo.tI_Application
ON dbo.Application
FOR INSERT AS

-- SAVE AT AT IDENTITY
DECLARE @IV INT
SET @IV = @@IDENTITY

--<<< Insert Statements That Reset @@Identity go Here >>>--

-- RESET AT AT IDENTITY
DECLARE @SQL VARCHAR(255)
SET @SQL = 'SELECT IDENTITY( int, ' + CONVERT( VARCHAR(10) , @IV ) + ' , 1 ) AS IDVal INTO #IVSTACK '
EXEC ( @SQL )

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-09-07 : 13:49:35

quote:
Originally posted by rawheiser

How to reset @@IDENTITY within a trigger in SQL 7.0 .....

DBCC CHECKIDENT

Tara

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources