Software Technology Tips

How to get the Deleted Identity Numbers in SQL Server 2005

Lets have an Employee table in which some employee records are saved with the unique identity numbers for each employee. Now, suppose some records were deleted from the Employee table due to some reasons and now I want all the deleted records from Employee table,which may not be possible now as records are deleted from the table,but we can get the list of Unique Identity records deleted from Employee table by using query.

To demonstrate this first of all we have to create an Employee Table with some records inserted to it.Then we can delete some selected records from the Employee table to verify the output.

-- Create Employee Table
Create Table EMPLOYEE( ID int,EmpID int,EmpName varchar(50))

-- Insert Sample data to the Employee table
INSERT INTO EMPLOYEE VALUES ( 1, 1, 'Aasish')
INSERT INTO EMPLOYEE VALUES ( 2, 1, 'Anita');
INSERT INTO EMPLOYEE VALUES ( 3, 1, 'Kabita');
INSERT INTO EMPLOYEE VALUES ( 4, 2, 'Raj');
INSERT INTO EMPLOYEE VALUES ( 5, 2, 'Babita');
INSERT INTO EMPLOYEE VALUES ( 6, 2, 'Geet');
INSERT INTO EMPLOYEE VALUES ( 7, 2, 'Krishh');
INSERT INTO EMPLOYEE VALUES ( 8, 3, 'Kabir');
INSERT INTO EMPLOYEE VALUES ( 9, 3, 'Rasmita');
INSERT INTO EMPLOYEE VALUES ( 10, 3, 'Sam');

-- Delete data from the Employee table
DELETE EMPLOYEE WHERE ID IN(1,10,12);

--- Retrive the Employee table data after deleting the records
SELECT ID FROM EMPLOYEE
--- Retrive the deleted unique identity numbers from the Employee table
WITH Missing (missnum, maxid)
AS
(
SELECT 1 AS missnum, (select max(id) from EMPLOYEE) UNION ALL SELECT missnum + 1, maxid FROM Missing
WHERE missnum < maxid
)
SELECT missnum FROM Missing
LEFT OUTER JOIN EMPLOYEE e on e.id = Missing.missnum
WHERE e.id is NULL OPTION (MAXRECURSION 0);


Related Tags:

Database

Author: Rasmita Mohanty

Database

Let us Connect!

iso 9001 QA25 Red Herring STPI D&B Fastest Growing SME 2013 Award zinnov Nasscom

This site uses cookies. We respect your privacy.copyright (c) Mindfire Solutions 2007-2015. Login