Get Deleted Identity - 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 2000 Forums

Transact-SQL (2000)

Get Deleted Identity

Author

Topic

jonasalbert20
Constraint Violating Yak Guru

300 Posts

Posted - 2008-04-10 : 21:16:20

I have an auto-incremented field. What I want is to retrieve the deleted incremented field. Let me show you an example...


transid
-------------------
1
2
deleted
4
5
deleted
7
deleted
9
10

Desired result:


transid_deleted
-------------------
3
6
8

thanks in advance.

For fast result follow this...
http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx

Want Philippines to become 1st World COuntry? Go for World War 3...

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2008-04-11 : 00:29:45

Get the script to create a numbers table function here:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=47685

Then just left join to it

DECLARE @max int
SELECT @max = max(transid) FROM yourtable
SELECT NUMBER 
FROM dbo.F_TABLE_NUMBER_RANGE(1,@max)
LEFT JOIN yourtable ON transid = NUMBER
WHERE transid IS NULL

Note that there are some good articles here on SQLTeam that explain more about identity columns or help you with creating your own custom sequences and so on, for example
http://www.sqlteam.com/article/custom-auto-generated-sequences-with-sql-server
http://www.sqlteam.com/article/understanding-identity-columns

jonasalbert20
Constraint Violating Yak Guru

300 Posts

Posted - 2008-04-11 : 01:51:44

thank you very much

For fast result follow this...
http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx

Want Philippines to become 1st World COuntry? Go for World War 3...

ranganath
Posting Yak Master

209 Posts

Posted - 2008-04-15 : 02:18:47

hi,
try with this

Declare @T Table (Id Int)
Insert into @T
Select 1 Union All
Select 2 Union All
Select 4 Union All
Select 5 Union All
Select 8 Union All
Select 10
--Select * From @T
Declare @MaxId Int
Select @MaxId = Max(Id) From @T

Select A.Number
From (Select Number From Master..Spt_Values where Type= 'P' and Number > 0)A
where Number not in (Select Id From @T) and Number < @MaxId

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-04-15 : 04:03:53

http://www.nigelrivett.net/SQLTsql/FindGapsInSequence.html

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources