Delete Duplicate Data From a Table

USE SAMPLE
GO
CREATE TABLE #TAB1
(ID INT,
NAME VARCHAR(10)
)

INSERT INTO #TAB1
SELECT 1, 'JOHN'

INSERT INTO #TAB1
SELECT 1, 'JOHN'


INSERT INTO #TAB1
SELECT 2, 'Stephen'

INSERT INTO #TAB1
SELECT 3, 'Sonia'

INSERT INTO #TAB1
SELECT 4, 'RIYA'

INSERT INTO #TAB1
SELECT 4, 'RIYA'

INSERT INTO #TAB1
SELECT 4, 'RIYA'

--SELECT * FROM #TAB1

--To Identify Duplicates
SELECT * FROM #TAB1
GROUP BY ID, NAME HAVING COUNT(*) > 1


--To Delete Duplicates from the Table

DECLARE @DupCount INT
DECLARE @NonDupCount INT
DECLARE @TotalCount INT
DECLARE @NAME VARCHAR(10)
DECLARE @ID INT

SELECT @TotalCount = COUNT(*) FROM #TAB1

SELECT * FROM #TAB1
GROUP BY ID, NAME HAVING COUNT(*) = 1
SELECT distinct ID, name FROM #TAB1

SET @NonDupCount = @@ROWCOUNT

SELECT @DupCount = @TotalCount - @NonDupCount
--Select distinct * from #TAB1

Select @DupCount as dup, @TotalCount as total, @NonDupCount as nondup

set @DupCount=@DupCount+4
SET ROWCOUNT 1

WHILE @DupCount > 0
BEGIN
--SELECT TOP 1 NAME , ID FROM #TAB1 GROUP BY ID, NAME HAVING COUNT(*) > 1
SELECT TOP 1 @NAME = NAME , @ID = ID FROM #TAB1 GROUP BY ID, NAME HAVING COUNT(*) > 1
--Select @name as Name , @id as ID

DELETE FROM #TAB1 WHERE NAME = @NAME AND ID = @ID
Select @name =null, @id =0
SET @DupCount = @DupCount - 1
END
SET ROWCOUNT 0
Select * from #TAB1


DROP TABLE #TAB1