SQL Delete duplicate records

David Smith

14y2.3k

I want to write query trigger to delete duplicate records, can someone assist me.

3 Replies

Know the answer? Post it — somebody with the same question will find it here.

Hermes Condez14y ago
If you mean that when "on inserting" a row and it has a duplicate, a trigger will execute to delete the duplicate, then, I think there is no need to have a trigger. All you have to do is modify the "INSERT" statement to insert only the record that has no duplicate.
0
Jignesh Trivedi14y ago
hi,

To doing some activety on table instead of inserting or updating u must write Insteadof trigger on table.

please refer,

http://www.mssqltips.com/sqlservertip/1804/using-instead-of-triggers-in-sql-server-for-dml-operations/
http://msdn.microsoft.com/en-us/library/aa224818%28v=sql.80%29.aspx

hope this help.
0

CREATE TRIGGER IO_Trig_INS_Employee ON Employee INSTEAD OF INSERT AS BEGIN SET NOCOUNT ON -- Check for duplicate Person. If no duplicate, do an insert. IF (NOT EXISTS (SELECT P.SSN       FROM Person P, inserted I       WHERE P.SSN = I.SSN))    INSERT INTO Person       SELECT SSN,Name,Address,Birthdate       FROM inserted ELSE -- Log attempt to insert duplicate Person row in PersonDuplicates table.    INSERT INTO PersonDuplicates       SELECT SSN,Name,Address,Birthdate,SUSER_SNAME(),GETDATE()       FROM inserted -- Check for duplicate Employee. If no duplicate, do an insert. IF (NOT EXISTS (SELECT E.SSN       FROM EmployeeTable E, inserted       WHERE E.SSN = inserted.SSN))    INSERT INTO EmployeeTable       SELECT EmployeeID,SSN, Department, Salary       FROM inserted ELSE --If duplicate, change to UPDATE so that there will not --be a duplicate key violation error.    UPDATE EmployeeTable       SET EmployeeID = I.EmployeeID,           Department = I.Department,           Salary = I.Salary    FROM EmployeeTable E, inserted I    WHERE E.SSN = I.SSN END