— 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);