The following script deletes the duplicates from ‘Employee’ table. If there are duplicate records then the script will get a count of the duplicate records and will then delete the records till the count is 1.

/*******************************************************************/
/* Script for deletion of Duplicate record from the Employee Table */
Declare @id int,
        @name varchar (50),
        @cnt int,
        @salary numeric

Declare getallrecords cursor local static For
 Select count (1), id, name, salary
   from employee (nolock)
   group by id, name,salary having count(1)>1
 
Open getallrecords

Fetch next from getallrecords into @cnt,@id,@name,@salary
--Cursor to check with all other records
While @@fetch_status=0
 Begin
  Set @cnt= @cnt-1
  Set rowcount @cnt

  -- Deleting the duplicate records. Observe that all fields are mentioned at the where condition
  Delete from employee where and
  and

  Set rowcount 0

  Fetch next from getallrecords into @cnt,@id,@name,@salary
 End

Close getallrecords
Deallocate getallrecords

*******************************************************************

The logic of the script is pretty simple; the select query retrieves all the records that are duplicates i.e. having Count greater than one. The result set is retrieved by opening a local cursor which fetches one row at a time.

Note here that the Count column is a part of the select query; this is used to identify the no of duplicate rows in the result set.

The row count has been set to (Value obtained from the Count Column – 1). SQL Server uses rowcount to stop processing the query after the specified numbers of rows are returned. The delete statement is executed only to delete the rows set by the Set rowcount command. Once the records have been deleted, the rowcount of SQL server is reset to the default value of 0.