Can I get the deleted identity in sql?

Posted by Allemahesh under Sql Server on 7/22/2013 | Points: 10 | Views : 1764 | Status : [Member] [MVP] | Replies : 3
If I delete certain rows from one the table. Can I get the deleted identity in sql? How?




Responses

Posted by: Ssj_Kumar on: 7/23/2013 [Member] Starter | Points: 25

Up
0
Down
WITH MissingValues (missnumber, maxnumber)
AS
(
SELECT 1 AS missnumber, (select max(ID) from Customer)
UNION ALL SELECT missnumber + 1, maxnumber FROM MissingValues
WHERE missnumber < maxnumber
)

SELECT missnumber FROM MissingValues
LEFT OUTER JOIN Customer e on e.ID = MissingValues.missnumber
WHERE e.OID is NULL OPTION (MAXRECURSION 0);

Regards,
Jayakumar Selvakani

Allemahesh, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Bandi on: 7/29/2013 [Member] [MVP] Platinum | Points: 25

Up
0
Down
The above solution (Jayakumar's post) has limitation....
If the last ID of customer is deleted by user, then the above query won't show the last missing identity value...
So you can take current identity value by using IDENT_CURRENT('TableName') instead of MAX(IdentitycolumnName)

DECLARE @maxIdVal int
SELECT @maxIdVal = IDENT_CURRENT('Customer') -- for example I took Customer table

;WITH MissingValues (missnumber, maxnumber)
AS
(
SELECT 1 AS missnumber, @maxIdVal
UNION ALL SELECT missnumber + 1, maxnumber FROM MissingValues
WHERE missnumber < maxnumber
)
SELECT missnumber FROM MissingValues
LEFT OUTER JOIN #CarType e on e.ID = MissingValues.missnumber
WHERE e.ID is NULL OPTION (MAXRECURSION 0);


Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Allemahesh, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: aswinialuri-19361 on: 7/30/2013 [Member] Starter | Points: 25

Login to post response