Get next identity value - SQL Server Forums

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

SQL Server 2005 Forums

Transact-SQL (2005)

Get next identity value

Author

Topic

waterduck
Aged Yak Warrior

982 Posts

Posted - 2009-09-11 : 22:50:03

[code]DECLARE @tempfun TABLE(col1 int identity(1,1) not null, col2 int)
INSERT INTO @tempfun SELECT
9 UNION ALL SELECT
8 UNION ALL SELECT
7
DELETE FROM @tempfun WHERE col1 = 3
INSERT INTO @tempfun SELECT 6
SELECT * FROM @tempfun[/code]
hi guys, we know that identity field will keep on accumulate but after we delete 1 row of data, how do we get the next identity value is what?
i means how to get the next identity value from a select statement??

Hope can help...but advise to wait pros with confirmation...

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-09-12 : 00:48:58

The DELETE doesn't affect the next value. If you've added 1-6 and delete 6, you still get 7.

You can use @@IDENTITY or SCOPE_IDENTITY to get the current value just inserted. To see it otherwise, you can use DBCC CHECKIDENT.

Check BOL for details.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."

waterduck
Aged Yak Warrior

982 Posts

Posted - 2009-09-15 : 11:07:37

sorry tara....haven wrong logic thinking that time...

Hope can help...but advise to wait pros with confirmation...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources