CODEWITHSUNDEEP
c#sqlsql-serverdatabasesql-server-2012
Question
Question

Reputation: 61

Delete from two related tables by useng ID returned from the first one

I want to delete 2 records from related tables(the record with the same id of foreign key) by using sqlcommand. Is there any method like SCOPE_IDENTITY that return the last ID has been deleted? I don't want to use ON DELETE CASCADE Constraint. Any idea?

Thanks.

Upvotes: 1

Views: 82

Answers (1)

Avijit
Avijit

Reputation: 1229

How to get the Deleted Identity Numbers in SQL Server:

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 we 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.

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

Upvotes: 1

Related Questions