SQL Delete duplicate records

Question

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

Hermes Condez · Answer

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.

Jignesh Trivedi · Answer

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.

Senthilkumar · Answer

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

SQL Delete duplicate records

Answers (3)