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 SSNNameAddressBirthdate FROM inserted ELSE -- Log attempt to insert duplicate Person row in PersonDuplicates table. INSERT INTO PersonDuplicates SELECT SSNNameAddressBirthdateSUSER_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 EmployeeIDSSN 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